| Groups | Blog | Home |
sql server programming : Stored Procedure Help
I'm trying to attempt to tie promotion information to orders based on dates.
So, if someone received an e-mail promotion of 1/1/2006, 2/1/2006, and 3/1/2006 and ordered something on 2/5/2006, I want to apply the e-mail promotion from 2/1/2006 to the order. Below is the only way I could come up with, but it takes over an hour to run with 1.2 million orders and 3.4 million promotions. Any help would be appreciated. CREATE PROCEDURE [dbo].[IR_UPDATE_ORDERS] ( @Client as Varchar(20), @paramDivisionCode as Char(2)) AS BEGIN DECLARE @Promotion as varchar(200), @PromotionID as Char(2), @InHomeDate as DateTime, @OrderDate as DateTime, @ClientIdentifier as VarChar(20), @DivisionCode as Char(2), @DayzDiff as int, @MatchCode as Char(30) -- Load up cursor with ClientIdentifier, DivisionCode, MatchCode, and OrderDate from IR_Orders BEGIN DECLARE curOrders Insensitive Cursor FOR SELECT ClientIdentifier, DivisionCode, MatchCode, OrderDate FROM IR_ORDERS where ClientIdentifier = @Client and DivisionCode = @paramDivisionCode ORDER BY ir_orders.clientidentifier, ir_orders.divisioncode, ir_orders.matchcode, ir_orders.orderdate desc FOR READ ONLY END SET NOCOUNT ON OPEN curOrders FETCH FROM curOrders into @ClientIdentifier, @DivisionCode, @MatchCode, @OrderDate while @@fetch_status = 0 Begin print ' Match ' + @MatchCode + ' InHome ' + ' OrderDate ' + convert(varchar(10),@OrderDate,110) Set @Purchase = (select top 1 IR_PURCHASE.PromotionCode from IR_PURCHASE where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND IR_PURCHASE.Division = @DivisionCode AND IR_PURCHASE.MatchCode = @MatchCode AND IR_PURCHASE.InHomeDate <= @OrderDate ORDER BY IR_PURCHASE.ClientIdentifier, IR_PURCHASE.Division, IR_PURCHASE.MatchCode, IR_PURCHASE.InHomeDate desc) set @InHomeDate = (select top 1 IR_PURCHASE.InHomeDate from IR_PURCHASE where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND IR_PURCHASE.Division = @DivisionCode AND IR_PURCHASE.MatchCode = @MatchCode AND IR_PURCHASE.InHomeDate <= @OrderDate ORDER BY IR_PURCHASE.ClientIdentifier, IR_PURCHASE.Division, IR_PURCHASE.MatchCode, IR_PURCHASE.InHomeDate desc) Set @PromotionID = (select top 1 IR_PURCHASE.PromotionID from IR_PURCHASE where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND IR_PURCHASE.Division = @DivisionCode AND IR_PURCHASE.MatchCode = @MatchCode AND IR_PURCHASE.InHomeDate <= @OrderDate ORDER BY IR_PURCHASE.ClientIdentifier, IR_PURCHASE.Division, IR_PURCHASE.MatchCode, IR_PURCHASE.InHomeDate desc) Set @DayzDiff = datediff(d,@InHomeDate,@OrderDate) Print 'Purchasecode ' + @Purchase + ' Match ' + @MatchCode + ' InHome ' + convert(varchar(10),@InHomeDate,110) + ' OrderDate ' + convert(varchar(10),@OrderDate,110) UPDATE IR_ORDERS SET PromotionCode = @Purchase, DaysDiff = @DayzDiff, PurchasetionID = @PurchasetionID, InHomeDate = @InHomeDate WHERE DivisionCode = @DivisionCode AND ClientIdentifier = @ClientIdentifier AND Matchcode = @Matchcode AND OrderDate = @OrderDate FETCH FROM curOrders into @ClientIdentifier, @DivisionCode, @MatchCode, @OrderDate END Close curOrders Deallocate curOrders END -- Mike Voissem
Please provide sample DDL and data.
Chris,
By DDL, do you mean the table and index scripts? Thanx, Mike -- Mike Voissem [quoted text, click to view] "Chris Lim" wrote:
> Please provide sample DDL and data. >
Okay, I googled DDL and SQL and learned once again : )
So, here's the DDL and some data. As you can see from the data, there are times when a purchaser may receive multiple promotions, and we have to apply the proper promotion to that order based on date. ORDERS TABLE ************************************************************ USE [InferredResponse] GO /****** Object: Table [dbo].[IR_Orders] Script Date: 08/12/2006 10:59:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[IR_Orders]( [MatchCode] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CustomerName] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Zip] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DivisionCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ClientIdentifier] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [OrderNumber] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [SourceCode] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PromotionCode] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OrderDate] [datetime] NULL, [DaysDiff] [int] NULL, [OrderAmount] [decimal](12, 2) NULL, [OrderID] [bigint] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_IR_Orders_1] PRIMARY KEY CLUSTERED ( [DivisionCode] ASC, [ClientIdentifier] ASC, [OrderNumber] ASC, [OrderID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Matchcode from client' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'MatchCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Customer Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'CustomerName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'City' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'City' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'State' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'State' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Zipcode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'Zip' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Division Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'DivisionCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ClientIdentifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'ClientIdentifier' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Order Number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'OrderNumber' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Promotion Code - assigned from promotions' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'PromotionCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Order Date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'OrderDate' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Difference between In-home date and order date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'DaysDiff' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Order Amount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'OrderAmount' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a unique ID because the orders wouldn''t always have an order number, and we needed a unique way to link back to orders when applying the inferred promotion code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_Orders', @level2type=N'COLUMN',@level2name=N'OrderID' GO /****** Object: Statistic [IX_Match_Div_Client] Script Date: 08/12/2006 10:59:08 ******/ CREATE STATISTICS [IX_Match_Div_Client] ON [dbo].[IR_Orders]([MatchCode], [DivisionCode], [ClientIdentifier]) GO /****** Object: Statistic [IX_Orders_DateDiv] Script Date: 08/12/2006 10:59:08 ******/ CREATE STATISTICS [IX_Orders_DateDiv] ON [dbo].[IR_Orders]([OrderDate], [DivisionCode]) GO /****** Object: Statistic [IX_Orders_DivClient] Script Date: 08/12/2006 10:59:08 ******/ CREATE STATISTICS [IX_Orders_DivClient] ON [dbo].[IR_Orders]([DivisionCode], [ClientIdentifier], [OrderDate], [MatchCode]) ************************************************************ PROMOTIONS TABLE ************************************************************ USE [InferredResponse] GO /****** Object: Table [dbo].[IR_PromotionHistory] Script Date: 08/12/2006 10:55:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[IR_PromotionHistory]( [MatchCode] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Zip] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Division] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ClientIdentifier] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PromotionDate] [datetime] NULL, [PromotionCode] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PromotionID] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is the matchcode provided on the client file, if it exists' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IR_PromotionHistory', @level2type=N'COLUMN',@level2name=N'MatchCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Full Name' , @level0type=N'SCHEMA',@level0name=N'dbo',
Michael,
I tried this, and the query only returned one promotioncode for all orders(the top 1 promotioncode if sorted desc) So, I'm going to work on the query for a while and see if I can get it to work. This is where queries start to get a little fuzzy for me....or, a lot of fuzzy -- Mike Voissem [quoted text, click to view] "Michael Keating" wrote:
> Hi, > > Cursors are dreadfully slow compared to queries huh. Here is a query that > extracts the data in the way you require; > > Table aa_Orders for order numbers and dates > Table aa_Promos for promotion numbers and dates > > SELECT > aa_Orders.OrderNumber, > aa_Orders.OrderDate, > MAX(Promos.PromoNumber) AS PromoNumber > MAX(Promos.PromoDate) AS PromoDate > > FROM > ( > SELECT > aa_Orders.OrderNumber, > dbo.aa_Promos.PromoNumber, > dbo.aa_Promos.PromoDate > FROM aa_Orders > INNER JOIN aa_Promos > ON dbo.aa_Orders.OrderDate >= dbo.aa_Promos.PromoDate > ) Promos > > INNER JOIN aa_Orders > ON Promos.OrderNumber = aa_Orders.OrderNumber > AND Promos.PromoDate <= aa_Orders.OrderDate > GROUP BY aa_Orders.OrderNumber, aa_Orders.OrderDate > > > This doesn't match the names used in your SP, but it should be a simple > process to apply this query as the SELECT clause in an update query for your > table structure. > > HTH > > MFK. > > > > > "Mike Voissem" <MikeVoissem@discussions.microsoft.com> wrote in message > news:7249FA8E-CB43-4168-A8E7-83B19F9E84FF@microsoft.com... > > I'm trying to attempt to tie promotion information to orders based on > > dates. > > So, if someone received an e-mail promotion of 1/1/2006, 2/1/2006, and > > 3/1/2006 and ordered something on 2/5/2006, I want to apply the e-mail > > promotion from 2/1/2006 to the order. Below is the only way I could come > > up > > with, but it takes over an hour to run with 1.2 million orders and 3.4 > > million promotions. Any help would be appreciated. > > > > CREATE PROCEDURE [dbo].[IR_UPDATE_ORDERS] > > ( @Client as Varchar(20), > > @paramDivisionCode as Char(2)) > > AS > > BEGIN > > DECLARE @Promotion as varchar(200), > > @PromotionID as Char(2), > > @InHomeDate as DateTime, > > @OrderDate as DateTime, > > @ClientIdentifier as VarChar(20), > > @DivisionCode as Char(2), > > @DayzDiff as int, > > @MatchCode as Char(30) > > -- Load up cursor with ClientIdentifier, DivisionCode, MatchCode, and > > OrderDate from IR_Orders > > BEGIN DECLARE curOrders Insensitive Cursor > > FOR > > SELECT ClientIdentifier, DivisionCode, MatchCode, OrderDate > > FROM IR_ORDERS > > where ClientIdentifier = @Client and DivisionCode = @paramDivisionCode > > ORDER BY ir_orders.clientidentifier, ir_orders.divisioncode, > > ir_orders.matchcode, > > ir_orders.orderdate desc > > FOR READ ONLY > > END > > SET NOCOUNT ON > > OPEN curOrders > > FETCH FROM curOrders into @ClientIdentifier, @DivisionCode, @MatchCode, > > @OrderDate > > while @@fetch_status = 0 > > Begin > > print ' Match ' + @MatchCode + ' InHome ' + ' OrderDate ' + > > convert(varchar(10),@OrderDate,110) > > Set @Purchase = (select top 1 IR_PURCHASE.PromotionCode from IR_PURCHASE > > where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND > > IR_PURCHASE.Division = @DivisionCode AND > > IR_PURCHASE.MatchCode = @MatchCode AND > > IR_PURCHASE.InHomeDate <= @OrderDate > > ORDER BY IR_PURCHASE.ClientIdentifier, > > IR_PURCHASE.Division, > > IR_PURCHASE.MatchCode, > > IR_PURCHASE.InHomeDate desc) > > set @InHomeDate = (select top 1 IR_PURCHASE.InHomeDate from IR_PURCHASE > > where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND > > IR_PURCHASE.Division = @DivisionCode AND > > IR_PURCHASE.MatchCode = @MatchCode AND > > IR_PURCHASE.InHomeDate <= @OrderDate > > ORDER BY IR_PURCHASE.ClientIdentifier, > > IR_PURCHASE.Division, > > IR_PURCHASE.MatchCode, > > IR_PURCHASE.InHomeDate desc) > > Set @PromotionID = (select top 1 IR_PURCHASE.PromotionID from IR_PURCHASE > > where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND > > IR_PURCHASE.Division = @DivisionCode AND > > IR_PURCHASE.MatchCode = @MatchCode AND > > IR_PURCHASE.InHomeDate <= @OrderDate > > ORDER BY IR_PURCHASE.ClientIdentifier, > > IR_PURCHASE.Division, > > IR_PURCHASE.MatchCode, > > IR_PURCHASE.InHomeDate desc) > > Set @DayzDiff = datediff(d,@InHomeDate,@OrderDate) > > Print 'Purchasecode ' + @Purchase + ' Match ' + @MatchCode + ' InHome ' + > > convert(varchar(10),@InHomeDate,110) + ' OrderDate ' + > > convert(varchar(10),@OrderDate,110) > > UPDATE IR_ORDERS > > SET PromotionCode = @Purchase, > > DaysDiff = @DayzDiff, > > PurchasetionID = @PurchasetionID, > > InHomeDate = @InHomeDate > > WHERE DivisionCode = @DivisionCode AND > > ClientIdentifier = @ClientIdentifier AND > > Matchcode = @Matchcode AND > > OrderDate = @OrderDate > > FETCH FROM curOrders into @ClientIdentifier, @DivisionCode, @MatchCode, > > @OrderDate > > END > > Close curOrders > > Deallocate curOrders > > END > > -- > > Mike Voissem > > > >
Hi,
Cursors are dreadfully slow compared to queries huh. Here is a query that extracts the data in the way you require; Table aa_Orders for order numbers and dates Table aa_Promos for promotion numbers and dates SELECT aa_Orders.OrderNumber, aa_Orders.OrderDate, MAX(Promos.PromoNumber) AS PromoNumber MAX(Promos.PromoDate) AS PromoDate FROM ( SELECT aa_Orders.OrderNumber, dbo.aa_Promos.PromoNumber, dbo.aa_Promos.PromoDate FROM aa_Orders INNER JOIN aa_Promos ON dbo.aa_Orders.OrderDate >= dbo.aa_Promos.PromoDate ) Promos INNER JOIN aa_Orders ON Promos.OrderNumber = aa_Orders.OrderNumber AND Promos.PromoDate <= aa_Orders.OrderDate GROUP BY aa_Orders.OrderNumber, aa_Orders.OrderDate This doesn't match the names used in your SP, but it should be a simple process to apply this query as the SELECT clause in an update query for your table structure. HTH MFK. [quoted text, click to view] "Mike Voissem" <MikeVoissem@discussions.microsoft.com> wrote in message news:7249FA8E-CB43-4168-A8E7-83B19F9E84FF@microsoft.com... > I'm trying to attempt to tie promotion information to orders based on > dates. > So, if someone received an e-mail promotion of 1/1/2006, 2/1/2006, and > 3/1/2006 and ordered something on 2/5/2006, I want to apply the e-mail > promotion from 2/1/2006 to the order. Below is the only way I could come > up > with, but it takes over an hour to run with 1.2 million orders and 3.4 > million promotions. Any help would be appreciated. > > CREATE PROCEDURE [dbo].[IR_UPDATE_ORDERS] > ( @Client as Varchar(20), > @paramDivisionCode as Char(2)) > AS > BEGIN > DECLARE @Promotion as varchar(200), > @PromotionID as Char(2), > @InHomeDate as DateTime, > @OrderDate as DateTime, > @ClientIdentifier as VarChar(20), > @DivisionCode as Char(2), > @DayzDiff as int, > @MatchCode as Char(30) > -- Load up cursor with ClientIdentifier, DivisionCode, MatchCode, and > OrderDate from IR_Orders > BEGIN DECLARE curOrders Insensitive Cursor > FOR > SELECT ClientIdentifier, DivisionCode, MatchCode, OrderDate > FROM IR_ORDERS > where ClientIdentifier = @Client and DivisionCode = @paramDivisionCode > ORDER BY ir_orders.clientidentifier, ir_orders.divisioncode, > ir_orders.matchcode, > ir_orders.orderdate desc > FOR READ ONLY > END > SET NOCOUNT ON > OPEN curOrders > FETCH FROM curOrders into @ClientIdentifier, @DivisionCode, @MatchCode, > @OrderDate > while @@fetch_status = 0 > Begin > print ' Match ' + @MatchCode + ' InHome ' + ' OrderDate ' + > convert(varchar(10),@OrderDate,110) > Set @Purchase = (select top 1 IR_PURCHASE.PromotionCode from IR_PURCHASE > where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND > IR_PURCHASE.Division = @DivisionCode AND > IR_PURCHASE.MatchCode = @MatchCode AND > IR_PURCHASE.InHomeDate <= @OrderDate > ORDER BY IR_PURCHASE.ClientIdentifier, > IR_PURCHASE.Division, > IR_PURCHASE.MatchCode, > IR_PURCHASE.InHomeDate desc) > set @InHomeDate = (select top 1 IR_PURCHASE.InHomeDate from IR_PURCHASE > where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND > IR_PURCHASE.Division = @DivisionCode AND > IR_PURCHASE.MatchCode = @MatchCode AND > IR_PURCHASE.InHomeDate <= @OrderDate > ORDER BY IR_PURCHASE.ClientIdentifier, > IR_PURCHASE.Division, > IR_PURCHASE.MatchCode, > IR_PURCHASE.InHomeDate desc) > Set @PromotionID = (select top 1 IR_PURCHASE.PromotionID from IR_PURCHASE > where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND > IR_PURCHASE.Division = @DivisionCode AND > IR_PURCHASE.MatchCode = @MatchCode AND > IR_PURCHASE.InHomeDate <= @OrderDate > ORDER BY IR_PURCHASE.ClientIdentifier, > IR_PURCHASE.Division, > IR_PURCHASE.MatchCode, > IR_PURCHASE.InHomeDate desc) > Set @DayzDiff = datediff(d,@InHomeDate,@OrderDate) > Print 'Purchasecode ' + @Purchase + ' Match ' + @MatchCode + ' InHome ' + > convert(varchar(10),@InHomeDate,110) + ' OrderDate ' + > convert(varchar(10),@OrderDate,110) > UPDATE IR_ORDERS > SET PromotionCode = @Purchase, > DaysDiff = @DayzDiff, > PurchasetionID = @PurchasetionID, > InHomeDate = @InHomeDate > WHERE DivisionCode = @DivisionCode AND > ClientIdentifier = @ClientIdentifier AND > Matchcode = @Matchcode AND > OrderDate = @OrderDate > FETCH FROM curOrders into @ClientIdentifier, @DivisionCode, @MatchCode, > @OrderDate > END > Close curOrders > Deallocate curOrders > END > -- > Mike Voissem >
[quoted text, click to view]
Mike Voissem wrote: > Michael, > I tried this, and the query only returned one promotioncode for all > orders(the top 1 promotioncode if sorted desc) > So, I'm going to work on the query for a while and see if I can get it to > work. This is where queries start to get a little fuzzy for me....or, a lot > of fuzzy Try this modified version of Michael's query: SELECT LatestPromos.OrderNumber, MAX(LatestPromos.PromoNumber) FROM ( SELECT aa_Orders.OrderNumber, MAX(Promos.PromoDate) AS PromoDate FROM ( SELECT aa_Orders.OrderNumber, dbo.aa_Promos.PromoNumber, dbo.aa_Promos.PromoDate FROM aa_Orders INNER JOIN aa_Promos ON aa_Promos.PromoDate <= aa_Orders.OrderDate ) Promos INNER JOIN aa_Orders ON aa_Orders.OrderNumber = Promos.OrderNumber AND aa_Orders.OrderDate >= Promos.PromoDate GROUP BY aa_Orders.OrderNumber ) LatestPromos INNER JOIN aa_Promos ON aa_Promos.OrderNumber = LatestPromos.OrderNumber AND aa_Promos.PromoDate = Latest_Promos.PromoDate GROUP BY LatestPromos.OrderNumber This will return the PromoNumber for the latest promotion for each order. I am assuming (but couldn't quite tell from your DDL as there were no keys that I could see) that PromoNumber is unique and therefore you can join back onto your promotion table to get the other details about that promotion. Chris
[quoted text, click to view]
Mike Voissem wrote: > Okay, I googled DDL and SQL and learned once again : ) > So, here's the DDL and some data. As you can see from the data, there are > times when a purchaser may receive multiple promotions, and we have to apply > the proper promotion to that order based on date. Thanks. Next time it would be better to omit things like "EXEC sys.sp_addextendedproperty" etc and just include tables, primary/foreign key constraints, and some sample Insert statements for data. Hopefully the modified version of Michael Keating's query I provided will give you enough to go on. Chris
Just to elaborate, this is how you would use the above query in your
UPDATE: UPDATE aa_Orders SET PromoNumber = aa_Promos.PromoNumber, DaysDiff = datediff(dd, aa_Promos.PromoDate, aa_Orders.OrderDate), InHomeDate = aa_Promots.PromoDate, etc FROM aa_Orders INNER JOIN ( SELECT LatestPromos.OrderNumber, MAX(LatestPromos.PromoNumber)AS PromoNumber FROM ( SELECT aa_Orders.OrderNumber, MAX(Promos.PromoDate) AS PromoDate FROM ( SELECT aa_Orders.OrderNumber, dbo.aa_Promos.PromoNumber, dbo.aa_Promos.PromoDate FROM aa_Orders INNER JOIN aa_Promos ON aa_Promos.PromoDate <= aa_Orders.OrderDate ) Promos INNER JOIN aa_Orders ON aa_Orders.OrderNumber = Promos.OrderNumber AND aa_Orders.OrderDate >= Promos.PromoDate GROUP BY aa_Orders.OrderNumber ) LatestPromos INNER JOIN aa_Promos ON aa_Promos.OrderNumber = LatestPromos.OrderNumber AND aa_Promos.PromoDate = Latest_Promos.PromoDate GROUP BY LatestPromos.OrderNumber ) P ON P.OrderNumber = aa_Orders.OrderNumber INNER JOIN aa_Promos ON aa_Promos.PromoNumber = P.PromoNumber
I forgot I took the key off the table. The key is MatchCode, PromotionDate,
PromotionCode, PromotionID So, I'm trying to modify your query but am totally lost. I think I need to take this in sections to digest or something. I really need to stop thinking in terms of sequential processing..... -- Mike Voissem [quoted text, click to view] "Chris Lim" wrote:
> Mike Voissem wrote: > > Michael, > > I tried this, and the query only returned one promotioncode for all > > orders(the top 1 promotioncode if sorted desc) > > So, I'm going to work on the query for a while and see if I can get it to > > work. This is where queries start to get a little fuzzy for me....or, a lot > > of fuzzy > > Try this modified version of Michael's query: > > SELECT LatestPromos.OrderNumber, > MAX(LatestPromos.PromoNumber) > FROM > ( > SELECT aa_Orders.OrderNumber, > MAX(Promos.PromoDate) AS PromoDate > FROM > ( > SELECT aa_Orders.OrderNumber, > dbo.aa_Promos.PromoNumber, > dbo.aa_Promos.PromoDate > FROM aa_Orders > INNER JOIN aa_Promos > ON aa_Promos.PromoDate <= aa_Orders.OrderDate > ) Promos > INNER JOIN aa_Orders > ON aa_Orders.OrderNumber = Promos.OrderNumber > AND aa_Orders.OrderDate >= Promos.PromoDate > GROUP BY aa_Orders.OrderNumber > ) LatestPromos > INNER JOIN aa_Promos > ON aa_Promos.OrderNumber = LatestPromos.OrderNumber > AND aa_Promos.PromoDate = Latest_Promos.PromoDate > GROUP BY > LatestPromos.OrderNumber > > This will return the PromoNumber for the latest promotion for each > order. I am assuming (but couldn't quite tell from your DDL as there > were no keys that I could see) that PromoNumber is unique and therefore > you can join back onto your promotion table to get the other details > about that promotion. > > Chris >
[quoted text, click to view]
Mike Voissem wrote: > I forgot I took the key off the table. The key is MatchCode, PromotionDate, > PromotionCode, PromotionID > So, I'm trying to modify your query but am totally lost. I think I need to > take this in sections to digest or something. I really need to stop thinking > in terms of sequential processing..... It's more difficult if you don't have a single key on your promotion table. Essentially what the query is doing is: Derived Table Promos: This gets all eligible promos (i.e. PromoDate <= OrderDate) for each order. SELECT aa_Orders.OrderNumber, dbo.aa_Promos.PromoNumber, dbo.aa_Promos.PromoDate FROM aa_Orders INNER JOIN aa_Promos ON aa_Promos.PromoDate <= aa_Orders.OrderDate Dervied Table LatestPromos This works out the latest PromoDate for each order using the above derived table: SELECT aa_Orders.OrderNumber, MAX(Promos.PromoDate) AS PromoDate FROM Derived Table Promos INNER JOIN aa_Orders ON aa_Orders.OrderNumber = Promos.OrderNumber GROUP BY aa_Orders.OrderNumber (Note: the following line wasn't needed after all: AND aa_Orders.OrderDate >= Promos.PromoDate ) Derived table P: This gets the latest PromoNumber for each Latest Promotion Date (in case there are multiple Promotions on the same date) using the above derived tables. SELECT LatestPromos.OrderNumber, MAX(LatestPromos.PromoNumber)AS PromoNumber FROM Derived Table LatestPromos INNER JOIN aa_Promos ON aa_Promos.OrderNumber = LatestPromos.OrderNumber AND aa_Promos.PromoDate = Latest_Promos.PromoDate GROUP BY LatestPromos.OrderNumber ) P And finally the UPDATE uses all this info to get the latest Promo and its details: UPDATE aa_Orders SET PromoNumber = aa_Promos.PromoNumber, DaysDiff = datediff(dd, aa_Promos.PromoDate, aa_Orders.OrderDate), InHomeDate = aa_Promots.PromoDate, etc FROM aa_Orders INNER JOIN Dervied table P ON P.OrderNumber = aa_Orders.OrderNumber INNER JOIN aa_Promos ON aa_Promos.PromoNumber = P.PromoNumber In summary the logic is: 1) Get all eligible promos for each order. 2) Get the latest promo date for each order using promos from step 1. 3) Get the latest promo number for the promo dates in step 2. 4) Use the promo number from step 3 to join to promo table to get additional details to update orders with. As I said, it's more difficult if you don't have a single ID for your promo table.... unfortunately I don't have time to work that out right now.... Good luck! Chris
Hi There,
I think you may like to try this. As per my understanding of your requirement, I think you are trying to update the promo code of a month in which it got you sale. So I will take simple data (sample) and try to update Promo ( -- Only fields required ,in case of multiple products , productcode/id could also be included) PromoCode PromoDate Client P1 20060101 C1 P2 20060201 C1 P3 20060301 C1 Sales SalesCode SalesDate Client PromoCode S1 20060205 C1 NULL now in order to update I am taking help of UPDATE FROM syntax (yo may like to use corelated suquery) UPDATE SALES SET PROMOCODE=P.PROMOCODE FROM SALES S, PROMO P WHERE S.CLIENT=P.CLIENT AND CONVERT(VARCHAR(6),P.PROMODATE,112) = CONVERT(VARCHAR(6),S.SALESDATE,112) After Update Sales SalesCode SalesDate Client PromoCode S1 20060205 C1 P2 I think this will help you. tell me what I missed. With Warm regards Jatinder Singh http://jatindersingh.blogspot.com http://sqloracle.tripod.com
[quoted text, click to view]
jsfromynr wrote: > UPDATE SALES SET PROMOCODE=P.PROMOCODE > FROM SALES S, PROMO P > WHERE S.CLIENT=P.CLIENT > AND CONVERT(VARCHAR(6),P.PROMODATE,112) = > CONVERT(VARCHAR(6),S.SALESDATE,112) > > After Update > Sales > SalesCode SalesDate Client PromoCode > S1 20060205 C1 P2 > > I think this will help you. tell me what I missed. I don't think you have understood the OP's requirements. There is no exact date match between promotions and orders.
Chris,
thanks for your time!! What you are telling may be right. but after looking at " I'm trying to attempt to tie promotion information to orders based on dates. So, if someone received an e-mail promotion of 1/1/2006, 2/1/2006, and 3/1/2006 and ordered something on 2/5/2006, I want to apply the e-mail promotion from 2/1/2006 to the order. " I think this only state that if in a GIVEN MONTH YEAR promotion has resulted in a SALE then the promo code of THAT Month should tied(attached ) to the SALE. if you have looked at the where clause it has NOT compared dates. it compared YEAR MONTH part of both dates. which I think what OP was looking for (I may be wrong in my assumption). With Warm regards Jatinder Singh http://jatindersingh.blogspot.com http://sqloracle.tripod.com
Given his original code:
select top 1 IR_PURCHASE.PromotionID from IR_PURCHASE where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND IR_PURCHASE.Division = @DivisionCode AND IR_PURCHASE.MatchCode = @MatchCode AND IR_PURCHASE.InHomeDate <= @OrderDate ORDER BY IR_PURCHASE.ClientIdentifier, IR_PURCHASE.Division, IR_PURCHASE.MatchCode, IR_PURCHASE.InHomeDate desc ....it seems like he's trying to get the latest promotion for each order, which is not necessarily in the same month, and even if it is may not be the only one in the month. Chris [quoted text, click to view] jsfromynr wrote:
> Chris, > > thanks for your time!! > What you are telling may be right. > but after looking at > " > I'm trying to attempt to tie promotion information to orders based on > dates. > So, if someone received an e-mail promotion of 1/1/2006, 2/1/2006, and > 3/1/2006 and ordered something on 2/5/2006, I want to apply the e-mail > promotion from 2/1/2006 to the order. > " > I think this only state that if in a GIVEN MONTH YEAR promotion has > resulted in a SALE then the promo code of THAT Month should > tied(attached ) to the SALE. > > if you have looked at the where clause it has NOT compared dates. it > compared YEAR MONTH part of both dates. which I think what OP was > looking for (I may be wrong in my assumption). > > > > With Warm regards > Jatinder Singh > http://jatindersingh.blogspot.com > http://sqloracle.tripod.com
Chris,
You are correct. There could be instances where the last prior promotion date is up to 6 months ago, so yes, Jatinder's example wouldn't work. I have reviewed your latest resolution, and to make sure both you and I are on the same page(or that I'm understanding you correctly), your statement "a single ID for your promo table", does that infer that I should put an identity field on the table? With the promotions table, there can be one household that receives 3 promotions, and another that received 5, and another 7 etc. So, that's why I need to tie that matchcode and do the date compare when applying the promotioncode to an order. If I match on matchcode, then I need to grab the promotioncode from the most recent promotion that matchcode received. Hopefully the example below will make sense: (cuz there are times I wonder if I do) PROMOTIONS Matchcode PromoDate PromoCode ABC123 11/25/2005 NOV05 CDF456 11/25/2005 NOV05 GHI789 11/25/2005 NOV05 ABC123 01/14/2006 JAN06 GHI789 01/14/2006 JAN06 ORDERS Matchcode OrderDate PromoCodeToApply ABC123 1/10/2006 NOV05 CDF456 1/20/2006 NOV05 GHI789 1/5/2006 NOV05 GHI789 1/20/2006 JAN06 I think I have a clearer understanding of how these subqueries all work together, and I'll keep looking at your example and trying to get that to work. I appreciate your assistance with this very much. -- Mike Voissem [quoted text, click to view] "Chris Lim" wrote:
> Given his original code: > > select top 1 IR_PURCHASE.PromotionID from IR_PURCHASE > where IR_PURCHASE.ClientIdentifier = @ClientIdentifier AND > IR_PURCHASE.Division = @DivisionCode AND > IR_PURCHASE.MatchCode = @MatchCode AND > IR_PURCHASE.InHomeDate <= @OrderDate > ORDER BY IR_PURCHASE.ClientIdentifier, > IR_PURCHASE.Division, > IR_PURCHASE.MatchCode, > IR_PURCHASE.InHomeDate desc > > ....it seems like he's trying to get the latest promotion for each > order, which is not necessarily in the same month, and even if it is > may not be the only one in the month. > > Chris > > jsfromynr wrote: > > Chris, > > > > thanks for your time!! > > What you are telling may be right. > > but after looking at > > " > > I'm trying to attempt to tie promotion information to orders based on > > dates. > > So, if someone received an e-mail promotion of 1/1/2006, 2/1/2006, and > > 3/1/2006 and ordered something on 2/5/2006, I want to apply the e-mail > > promotion from 2/1/2006 to the order. > > " > > I think this only state that if in a GIVEN MONTH YEAR promotion has > > resulted in a SALE then the promo code of THAT Month should > > tied(attached ) to the SALE. > > > > if you have looked at the where clause it has NOT compared dates. it > > compared YEAR MONTH part of both dates. which I think what OP was > > looking for (I may be wrong in my assumption). > > > > > > > > With Warm regards > > Jatinder Singh > > http://jatindersingh.blogspot.com > > http://sqloracle.tripod.com >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |