Query to display most active records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to display most active records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-14 : 08:08:21

Dave writes "Hello,

My server is Windows 2K SP4, SQL Server 2K SP3a.

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.

D"

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-14 : 12:05:59

Instead of describing the tables, why don't you just supply the DDL used to create the tables? Give us some sample data (in the form of INSERT statements), a desired output and a quick description of the business rules to be used.

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

uxphreak
Starting Member

38 Posts

Posted - 2005-02-14 : 18:56:07

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

Here's the output from my current query, which is part of my post:

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 116 60.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 2 116 60.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 3 116 60.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 6 116 60.0000 0.20000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 1 116 60.0000 0.40000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 2 116 60.0000 0.40000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 3 116 60.0000 0.40000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 4 116 60.0000 0.40000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 6 116 60.0000 0.40000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 7 116 60.0000 0.40000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 8 116 60.0000 0.40000000000000001

And here's the output I would like to see:

promoid startdate enddate skuid promoprice disclevel priority
--------- ---------------------- ----------------------- ----- ---------- ------------------- --------
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 116 60.0000 0.20000000000000001 1
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 116 60.0000 0.20000000000000001 2
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 23:59:59.000 116 60.0000 0.40000000000000001 3

Thanks,

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources