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

Please provide sample DDL and data.

Chris,
By DDL, do you mean the table and index scripts?
Thanx,
Mike
Show quoteHide quote

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',
@level1type=N'TABLE',@level1name=N'IR_PromotionHistory',
@level2type=N'COLUMN',@level2name=N'FullName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'City' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'IR_PromotionHistory',
@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_PromotionHistory',
@level2type=N'COLUMN',@level2name=N'State'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Zip' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'IR_PromotionHistory',
@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_PromotionHistory',
@level2type=N'COLUMN',@level2name=N'Division'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Client
Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'IR_PromotionHistory',
@level2type=N'COLUMN',@level2name=N'ClientIdentifier'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Promotion
Date' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'IR_PromotionHistory',
@level2type=N'COLUMN',@level2name=N'PromotionDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Promotion
Code' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'IR_PromotionHistory',
@level2type=N'COLUMN',@level2name=N'PromotionCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Promotion
Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'IR_PromotionHistory',
@level2type=N'COLUMN',@level2name=N'PromotionID'
GO


************************************************************
Purchase - Orders Data
************************************************************

MatchCode    OrderNumber    Division    ClientIdentifier    OrderDate    OrderAmount    OrderID
AABYE1481*80921                   JSNOWA09N5R                       00    CLIENTABC  
            05/06/06    49.00    612061
AABYE1481*80921                   PECKEA09N5P                       00    CLIENTABC  
            05/06/06    49.00    612060
AACKE2914*98108                   VHAACA09EHV                       00    CLIENTABC  
            04/27/06    39.00    261222
AADAT2323*77057                   AZARKA09XVA                       00    CLIENTABC  
            05/10/06    49.00    74585
AADER2340491355                   SBAADA09LPV                       00    CLIENTABC  
            05/04/06    44.00    152349
AAGAS9503891395                   PCANLA09RKG                       00    CLIENTABC  
            05/08/06    39.00    381962
AAGEN1841*48382                   EDOHRA09WK2                       00    CLIENTABC  
            05/10/06    56.00    744386
AALBU3819*98208                   LAALBA09JQN                       00    CLIENTABC  
            05/03/06    39.00    568701
AALIM5125*23464                   ICAALA09EE0                       00    CLIENTABC  
            04/27/06    39.00    350023
AANON6558*90630                   KAANOA0A6UY                       00    CLIENTABC  
            05/12/06    52.00    938464
AANTA23***05453                   AMURRA09TMG                       00    CLIENTABC  
            05/09/06    39.00    986298
AAPER728**60661                   EMARTA09QQQ                       00    CLIENTABC  
            05/08/06    39.00    323338
AAPER728**60661                   JLAAPA09QM4                       00    CLIENTABC  
            05/08/06    39.00    323337
AARON111**11577                   CAAROA0A6V2                       00    CLIENTABC  
            05/12/06    49.00    938738
AARON12***03053                   AWECHA09H32                       00    CLIENTABC  
            05/02/06    49.00    400073
AARON1268692130                   BSIMOA09TLR                       00    CLIENTABC  
            05/09/06    99.00    696672
AARON1298*11557                   DMINSA0A56Z                       00    CLIENTABC  
            05/12/06    71.00    331740
AARON1298*11557                   HAAROA09XW8                       00    CLIENTABC  
            05/10/06    97.00    331741
AARON1402*20170                   CAAROA09FU3                       00    CLIENTABC  
            04/29/06    39.00    287706
AARON1816*60087                   CAAROA0A69V                       00    CLIENTABC  
            05/12/06    49.00    168360
AARON1816*60087                   IFERGA0A69D                       00    CLIENTABC  
            05/12/06    49.00    168361
AARON223**08854                   AAAROA09LZU                       00    CLIENTABC  
            05/05/06    49.00    188635
AARON223**08854                   BAAROA09M04                       00    CLIENTABC  
            05/05/06    49.00    188636
AARON223**08854                   JNODEA09LZP                       00    CLIENTABC  
            05/05/06    49.00    188637
AARON2314*23233                   BFURIA0A2MM                       00    CLIENTABC  
            05/11/06    44.00    859921
AARON3313*48911                   FAAROA09NH3                       00    CLIENTABC  
            05/06/06    49.00    384161
AARON3347*48084                   EAAROA09KUG                       00    CLIENTABC  
            05/04/06    39.00    580414
AARON3455*30326                   JAAROA09RNW                       00    CLIENTABC  
            05/08/06    59.00    438380
AARON3455*30326                   SLIPSA09RN5                       00    CLIENTABC  
            05/08/06    59.00    438381
AARON3828*27539                   MKWITA0A2V0                       00    CLIENTABC  
            05/11/06    44.00    582004
AARON3828*27539                   PAAROA0A2UN                       00    CLIENTABC  
            05/11/06    44.00    582005
AARON3930*60613                   DAAROA09PAD                       00    CLIENTABC  
            05/07/06    39.00    626548
AARON421**11762                   NAAROA09SYF                       00    CLIENTABC  
            05/09/06    49.00    296761
AARON421**11762                   PCOOKA09SYX                       00    CLIENTABC  
            05/09/06    49.00    296762
AARON421**11762                   SDEPIA09SZD                       00    CLIENTABC  
            05/09/06    49.00    296760
AARON590**47909                   DAAROA09WXK                       00    CLIENTABC  
            05/10/06    39.00    294254
AARON9****02090                   EPOLLA0A0YZ                       00    CLIENTABC  
            05/11/06    44.00    827159
AARON927**29407                   CAAROA09UXK                       00    CLIENTABC  
            05/09/06    49.00    719975
AARON9550*30350                   CAAROA09DWL                       00    CLIENTABC  
            04/26/06    59.00    248890
AARON9550*30350                   CAAROA09DWS                       00    CLIENTABC  
            04/26/06    54.00    248891
AASCH465**01545                   KRAASA09V6X                       00    CLIENTABC  
            05/09/06    39.00    152684
AASCH723**93062                   SRAASA09JTL                       00    CLIENTABC  
            05/03/06    66.00    295023
AASE*1101*55420                   SELKIA09E0K                       00    CLIENTABC  
            04/26/06    39.00    366815
AASE*184**97501                   BAASEA09Q1D                       00    CLIENTABC  
            05/08/06    44.00    332936
AASE*5055*92886                   CLAIRA09Y0V                       00    CLIENTABC  
            05/10/06    49.00    73857
AASE*5055*92886                   MBECKA09Y1Q                       00    CLIENTABC  
            05/10/06    49.00    73858
AASEN1340185268                   DMAASA0A5WD                       00    CLIENTABC  
            05/12/06    49.00    330663
AASEN1647*94551                   BKISIA0A6BY                       00    CLIENTABC  
            05/12/06    49.00    918412
AASEN2557094552                   DLEIBA0A8ZB                       00    CLIENTABC  
            05/17/06    65.00    961282
AASMA1910*77545                   AJAASA09UAN                       00    CLIENTABC  
            05/09/06    54.00    287132
AASMA1910*77545                   DJAASA09U9G                       00    CLIENTABC  
            05/09/06    54.00    287134
AASMA1910*77545                   HKOOPA09UA3                       00    CLIENTABC  
            05/09/06    44.00    287133
AASS*244**10023                   LAASSA0A4WM                       00    CLIENTABC  
            05/12/06    46.00    901933
AATEN1551*55128                   ABRAAA09ERL                       00    CLIENTABC  
            04/27/06    39.00    130205
AATEN1580592394                   HPERTA0A4LY                       00    CLIENTABC  
            05/11/06    49.00    319678
AAVEN21***55812                   SRANTA09DTE                       00    CLIENTABC  
            04/26/06    39.00    282996
AAVIG2140*98370                   MHAAVA09Q2B                       00    CLIENTABC  
            05/08/06    64.00    173838
ABACH1294191710                   LSLABA0A115                       00    CLIENTABC  
            05/11/06    67.00    300361
ABACH204**72110                   HALABA09Z71                       00    CLIENTABC  
            05/10/06    39.00    333059
ABACK1587285379                   GSTABA0A12Y                       00    CLIENTABC  
            05/11/06    74.00    38472
ABACK255**10025                   JMICHA09QJH                       00    CLIENTABC  
            05/08/06    39.00    418627
ABACK42***11559                   PHERBA09SMR                       00    CLIENTABC  
            05/09/06    66.00    125880
ABAD*1427*60195                   BBAKEA09FHQ                       00    CLIENTABC  
            04/28/06    54.00    350182
ABAD*9830*91737                   SABADA0A0E1                       00    CLIENTABC  
            05/10/06    44.00    342146
ABADA6515*90723                   IENRIA09QDU                       00    CLIENTABC  
            05/08/06    56.00    641632
ABADA6515*90723                   IENRIA09QFK                       00    CLIENTABC  
            05/08/06    59.00    641634
ABADA6515*90723                   SENRIA09R0E                       00    CLIENTABC  
            05/08/06    49.00    641633
ABAGO1321*85629                   HSTOYA09EHD                       00    CLIENTABC  
            04/27/06    39.00    190848
ABAGO1321*85629                   MSCHAA09EH6                       00    CLIENTABC  
            04/27/06    39.00    190849
ABAGO430**91746                   JHERNA09S4J                       00    CLIENTABC  
            05/08/06    49.00    99927
ABAGO430**91746                   MRABAA09S4V                       00    CLIENTABC  
            05/08/06    51.00    99928
ABAHO105**07632                   PFURTA09VQP                       00    CLIENTABC  
            05/09/06    44.00    199179
ABAID612**27713                   LBARBA09RUZ                       00    CLIENTABC  
            05/08/06    39.00    474711
ABAIE117**28110                   JDEPAA09MCY                       00    CLIENTABC  
            05/05/06    39.00    304770
ABAJA9743*34786                   JBATLA09D8H                       00    CLIENTABC  
            04/25/06    39.00    504246
ABAKA3662*53110                   VTABAA09QAS                       00    CLIENTABC  
            05/08/06    39.00    989660
ABAKA5425*22309                   JRANDA09MMU                       00    CLIENTABC  
            05/05/06    61.00    376251
ABAKA5425*22309                   ECOOPA09E3L                       00    CLIENTABC  
            04/26/06    55.00    376250
ABAKA904**15223                   DTABAA09KM5                       00    CLIENTABC  
            05/04/06    61.00    578113
ABALA1111*92701                   ACHABA09JAF                       00    CLIENTABC  
            05/03/06    74.00    35200
ABALA137**15017                   BCHABA09H7U                       00    CLIENTABC  
            05/02/06    49.00    325249
ABALA137**15017                   SCHABA09H84                       00    CLIENTABC  
            05/02/06    49.00    325250
ABALA1678392555                   EMONTA09VWF                       00    CLIENTABC  
            05/09/06    39.00    220856
ABALA336**34102                   PMARTA09NVE                       00    CLIENTABC  
            05/07/06    61.00    490608
ABALA4618820165                   MZABAA0A3HP                       00    CLIENTABC  
            05/11/06    49.00    59929
ABALA4618820165                   ESIMPA09FEC                       00    CLIENTABC  
            04/28/06    53.00    59928
ABALA4618820165                   RBELLA09FEN                       00    CLIENTABC  
            04/28/06    46.00    59930
ABALA4618820165                   RMCCRA09FEE                       00    CLIENTABC  
            04/28/06    44.00    59931
ABANA122**60108                   LLABAA0A5NB                       00    CLIENTABC  
            05/12/06    49.00    916873
ABANA237**33947                   JFIGLA09P1J                       00    CLIENTABC  
            05/07/06    52.00    196928
ABAND1885192648                   JSCHNA09KZ0                       00    CLIENTABC  
            05/04/06    48.00    62022
ABAND3831*22204                   KBRABA09HQE                       00    CLIENTABC  
            05/02/06    39.00    209195
ABANE14***08540                   EFIABA0A8FZ                       00    CLIENTABC  
            05/16/06    53.00    25791
ABANG320**90020                   DCABAA09N2C                       00    CLIENTABC  
            05/06/06    59.00    17485
ABANG320**90020                   TPANNA09N2D                       00    CLIENTABC  
            05/06/06    44.00    17486
ABANI2650*34239                   KFUQUA09HGA                       00    CLIENTABC  
            05/02/06    42.00    297428
ABANI2650*34239                   KFUQUA09HRD                       00    CLIENTABC  
            05/02/06    32.00    297429
ABANI2650*34239                   MFUQUA09HGM                       00    CLIENTABC  
            05/02/06    49.00    297516
ABANI2650*34239                   MFUQUA09HGU                       00    CLIENTABC  
            05/02/06    49.00    297430
ABANI2761*90210                   NSNITA09JDG                       00    CLIENTABC  
            05/03/06    44.00    281989

************************************************************
Promotions Data
************************************************************
MatchCode    Division    ClientIdentifier    PromotionCode    PromotionID    PromotionDate
AABYE1481*80921                   00    CLIENTABC               ABA4       
    R1    05/01/06
AABYE1481*80921                   00    CLIENTABC               ABA4       
    R1    05/01/06
AACKE2914*98108                   00    CLIENTABC               Z304    Z1    04/01/06
AADAT2323*77057                   00    CLIENTABC               302    R1    05/01/06
AADER2340491355                   00    CLIENTABC               203    R1    05/01/06
AAGAS9503891395                   00    CLIENTABC               CATREQ     
    R1    05/01/06
AAGEN1841*48382                   00    CLIENTABC               WLK9       
    R1    05/01/06
AALBU3819*98208                   00    CLIENTABC               Z1002    Z1    04/01/06
AALIM5125*23464                   00    CLIENTABC               303    R1    04/01/06
AANON6558*90630                   00    CLIENTABC               IBE2       
    R1    05/01/06
AANTA23***05453                   00    CLIENTABC               604    R1    05/01/06
AAPER728**60661                   00    CLIENTABC               503    R1    05/01/06
AAPER728**60661                   00    CLIENTABC               503    R1    05/01/06
AARON111**11577                   00    CLIENTABC               WLK9       
    R1    05/01/06
AARON12***03053                   00    CLIENTABC               104    R1    04/01/06
AARON1268692130                   00    CLIENTABC               ABA6       
    R1    04/01/06
AARON1298*11557                   00    CLIENTABC               502    R1    05/01/06
AARON1298*11557                   00    CLIENTABC               502    R1    05/01/06
AARON1402*20170                   00    CLIENTABC               Z303    Z1    04/01/06
AARON1816*60087                   00    CLIENTABC               602    R1    05/01/06
AARON1816*60087                   00    CLIENTABC               602    R1    05/01/06
AARON223**08854                   00    CLIENTABC               503    R1    05/01/06
AARON223**08854                   00    CLIENTABC               503    R1    05/01/06
AARON223**08854                   00    CLIENTABC               503    R1    05/01/06
AARON2314*23233                   00    CLIENTABC               304    R1    05/01/06
AARON3313*48911                   00    CLIENTABC               WLK9       
    R1    05/01/06
AARON3347*48084                   00    CLIENTABC               502    R1    05/01/06
AARON3455*30326                   00    CLIENTABC               505    R1    05/01/06
AARON3455*30326                   00    CLIENTABC               505    R1    05/01/06
AARON3828*27539                   00    CLIENTABC               ABA8       
    R1    05/01/06
AARON3828*27539                   00    CLIENTABC               ABA8       
    R1    05/01/06
AARON3930*60613                   00    CLIENTABC               MABA4      
    R1    05/01/06
AARON421**11762                   00    CLIENTABC               504    R1    05/01/06
AARON421**11762                   00    CLIENTABC               504    R1    05/01/06
AARON421**11762                   00    CLIENTABC               504    R1    05/01/06
AARON590**47909                   00    CLIENTABC               302    R1    05/01/06
AARON9****02090                   00    CLIENTABC               ABA3       
    R1    05/01/06
AARON927**29407                   00    CLIENTABC               502    R1    05/01/06
AARON9550*30350                   00    CLIENTABC               Z102    Z1    04/01/06
AARON9550*30350                   00    CLIENTABC               Z102    Z1    04/01/06
AASCH465**01545                   00    CLIENTABC               503    R1    05/01/06
AASCH723**93062                   00    CLIENTABC               Z304    Z1    04/01/06
AASE*1101*55420                   00    CLIENTABC               ZWLK2       
    Z1    04/01/06
AASE*184**97501                   00    CLIENTABC               603    R1    05/01/06
AASE*5055*92886                   00    CLIENTABC               302    R1    05/01/06
AASE*5055*92886                   00    CLIENTABC               302    R1    05/01/06
AASEN1340185268                   00    CLIENTABC               505    R1    05/01/06
AASEN1647*94551                   00    CLIENTABC               WLK9       
    R1    05/01/06
AASEN2557094552                   00    CLIENTABC               WLK2       
    R1    05/01/06
AASMA1910*77545                   00    CLIENTABC               505    R1    05/01/06
AASMA1910*77545                   00    CLIENTABC               505    R1    05/01/06
AASMA1910*77545                   00    CLIENTABC               505    R1    05/01/06
AASS*244**10023                   00    CLIENTABC               802    R1    05/01/06
AATEN1551*55128                   00    CLIENTABC               Z305    Z1    04/01/06
AATEN1580592394                   00    CLIENTABC               405    R1    05/01/06
AAVEN21***55812                   00    CLIENTABC               Z502    Z1    04/01/06
AAVIG2140*98370                   00    CLIENTABC               604    R1    05/01/06
ABACH1294191710                   00    CLIENTABC               405    R1    05/01/06
ABACH204**72110                   00    CLIENTABC               604    R1    05/01/06
ABACK1587285379                   00    CLIENTABC               802    R1    05/01/06
ABACK255**10025                   00    CLIENTABC               102    R1    05/01/06
ABACK42***11559                   00    CLIENTABC               604    R1    05/01/06
ABAD*1427*60195                   00    CLIENTABC               Z305    Z1    04/01/06
ABAD*9830*91737                   00    CLIENTABC               602    R1    05/01/06
ABADA6515*90723                   00    CLIENTABC               202    R1    05/01/06
ABADA6515*90723                   00    CLIENTABC               202    R1    05/01/06
ABADA6515*90723                   00    CLIENTABC               202    R1    05/01/06
ABAGO1321*85629                   00    CLIENTABC               Z305    Z1    04/01/06
ABAGO1321*85629                   00    CLIENTABC               Z305    Z1    04/01/06
ABAGO430**91746                   00    CLIENTABC               103    R1    05/01/06
ABAGO430**91746                   00    CLIENTABC               103    R1    05/01/06
ABAHO105**07632                   00    CLIENTABC               205    R1    05/01/06
ABAID612**27713                   00    CLIENTABC               604    R1    05/01/06
ABAIE117**28110                   00    CLIENTABC               205    R1    05/01/06
ABAJA9743*34786                   00    CLIENTABC               Z504    Z1    04/01/06
ABAKA3662*53110                   00    CLIENTABC               103    R1    05/01/06
ABAKA5425*22309                   00    CLIENTABC               IBE1       
    R1    05/01/06
ABAKA5425*22309                   00    CLIENTABC               ZIBE1       
    Z1    04/01/06
ABAKA904**15223                   00    CLIENTABC               1002    R1    05/01/06
ABALA1111*92701                   00    CLIENTABC               Z402    Z1    04/01/06
ABALA137**15017                   00    CLIENTABC               Z302    Z1    04/01/06
ABALA137**15017                   00    CLIENTABC               Z302    Z1    04/01/06
ABALA1678392555                   00    CLIENTABC               404    R1    05/01/06
ABALA336**34102                   00    CLIENTABC               MWLK2      
    R1    05/01/06
ABALA4618820165                   00    CLIENTABC               103    R1    05/01/06
ABALA4618820165                   00    CLIENTABC               Z103    Z1    04/01/06
ABALA4618820165                   00    CLIENTABC               Z103    Z1    04/01/06
ABALA4618820165                   00    CLIENTABC               Z103    Z1    04/01/06
ABANA122**60108                   00    CLIENTABC               NEX4       
    R1    05/01/06
ABANA237**33947                   00    CLIENTABC               105    R1    05/01/06
ABAND1885192648                   00    CLIENTABC               304    R1    05/01/06
ABAND3831*22204                   00    CLIENTABC               Z205    Z1    04/01/06
ABANE14***08540                   00    CLIENTABC               305    R1    05/01/06
ABANG320**90020                   00    CLIENTABC               MWLK9      
    R1    05/01/06
ABANG320**90020                   00    CLIENTABC               MWLK9      
    R1    05/01/06
ABANI2650*34239                   00    CLIENTABC               Z403    Z1    04/01/06
ABANI2650*34239                   00    CLIENTABC               Z403    Z1    04/01/06
ABANI2650*34239                   00    CLIENTABC               Z403    Z1    04/01/06
ABANI2650*34239                   00    CLIENTABC               Z403    Z1    04/01/06
ABANI2761*90210                   00    CLIENTABC               Z303    Z1    04/01/06

Show quoteHide quote

Mike Voissem wrote:
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

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.




Show quoteHide quote

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
Show quoteHide quote

Mike Voissem wrote:
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

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.....
Show quoteHide quote

Mike Voissem wrote:
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

jsfromynr wrote:
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

jsfromynr wrote:
Show quoteHide quote

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.

Show quoteHide quote

Conversion failed when converting datetime from character string.
Variable table name in a cursor declaration
dynamic SQL versus a huge IF structure
Need Query help with JOIN
Is there an eval() function in sql server 2000?