Query to display most active records

Hello,

I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.

There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.

I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.

For example:

PromoID StartDate EndDate
--- --- ---
PROMO1 1/1/2004 1/1/2006 (1st Active Promotion)
PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion)
PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)

Here's a query I am using to display all active promotions:

select
pl.promoid,
pr.startdate,
pr.enddate,
pl.locid,
pl.skuid,
pl.promoprice,
pl.disclevel
from
promolocation pl
inner join
promotion pr
on
pl.promoid = pr.promoid
where
pr.enddate >= getdate()

Thanks for your help.

DProvide DDL and sample data. For example, SKU is missing from your post.|||Here's the DDL:

CREATE TABLE [dbo].[Promotion] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PromoLocation] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PromoPrice] [money] NULL ,
[DiscLevel] [float] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

And here's some of the output from my query:

promoid startdate enddate locid skuid promoprice disclevel
---- ---------------- ---------------- ------ ------ ------- ----------------
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 116 60.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 1 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 2 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 3 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 6 118B 99.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 119 139.0000 0.20000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 1 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 2 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 3 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 4 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 6 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 7 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 8 125 135.0000 0.59999999999999998
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 20 220.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 20 220.0000 0.20000000000000001

(25 row(s) affected)|||Can somone help me with this problem?

Thanks,

D|||Can somone help me with this problem?what was the question?

:)