Query to identify 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

Transact-SQL (2000)

Query to identify most active records

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-02-17 : 13:16:06

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.

D

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-17 : 13:35:46

If you do the extra legwork of providing the DDL for you tables AND insert statements to populate sample data, I'm sure you'll get some assistance with this.

BTW: try to avoid cross posting.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45891

Be One with the Optimizer
TG

uxphreak
Starting Member

38 Posts

Posted - 2005-02-17 : 13:44:04

Sorry for the cross-post, however, there seems to be no response.

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-17 : 13:47:03

quote:
AND insert statements to populate sample data

Where's the data?

Be One with the Optimizer
TG

uxphreak
Starting Member

38 Posts

Posted - 2005-02-17 : 15:10:24

The data already exists in the tables. I'm not sure what data you're asking for. As for the Insert statements, I'm not inserting data at this time.

D

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-17 : 15:27:15

Sorry uxphreak, I guess that wasn't clear.
What I'm asking for is you to create some insert statements so I/we can add 'sample' data to tables that we will create using the DDL you provided.
I/we need that to test the results of our efforts to come up with a query for you. Its a lot of work to do that but it's your issue, so you should do the work to make our part easier.

Here is an example of an insert statement:
insert Promotion Values ('PromoID1','LocID1','SkuID1',10.25,1.23,newid())

Be One with the Optimizer
TG

uxphreak
Starting Member

38 Posts

Posted - 2005-02-17 : 16:04:49

Please pardon my ignorance. I'm a relative newbee at this. Here's the insert statement for the promotion table:

insert into
promotion
(PromoID
,StartDate
,EndDate)
values
('SepNov20%'
,'2004-09-15'
,'2006-01-01')
insert into
promotion
(PromoID
,StartDate
,EndDate)
values
('20%_2'
,'2004-12-06'
,'2006-01-01 ')
insert into
promotion
(PromoID
,StartDate
,EndDate)
values
('Feb05Sale'
,'2005-02-11'
,'2005-02-19')

And here's the insert statement for the PromoLocation table:

insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'1'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'2'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'3'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'6'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'1'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'2'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'3'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'6'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'1'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'2'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'3'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'4'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'6'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'7'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'8'
,'116'
,60
,'.40')

Thanks,

D

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-17 : 17:34:44

This works for you sample data. If you can come up with additional data that would break it, then post those inserts and the desired results.


declare @resultsWithPriority Table
	(PromoID	char(10)
	,StartDate	datetime
	,EndDate	datetime
	,skuid		char(16)
	,disclevel	float
	,priority	int identity(1,1))
	
insert @resultsWithPriority 
Select	p.PromoID
	,StartDate
	,EndDate
	,skuid
	,max(disclevel) disclevel
from	Promotion p
JOIN	Promolocation pl ON p.promoid = pl.promoid
Where	enddate >= getdate()
Group by p.PromoID
	,StartDate
	,EndDate
	,skuid
order by EndDate desc
	,StartDate asc

Select * from @resultsWithPriority order by priority

Be One with the Optimizer
TG

uxphreak
Starting Member

38 Posts

Posted - 2005-02-17 : 18:36:41

Thanks TG, but the query did break once I applied it to the tables that have more data. It appears to work fine for one SKU, however, if more than one SKU exists in the same PromoID, then the priority increments for each SKU in that promotion.

Here's some of the output I received:

PromoID StartDate EndDate skuid disclevel priority
------- ----------------------- ----------------------- ----- --------- -----------
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 3026 NULL 1
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 3027 NULL 2
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 3061 NULL 3
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 3213 NULL 4
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 3063 NULL 5
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 3102 NULL 6
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 4406 NULL 7
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 503 NULL 8
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5102 NULL 9
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5204 NULL 10
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5215 NULL 11
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5216 NULL 12
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5218 NULL 13
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5219 NULL 14
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5221 NULL 15
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5224 NULL 16
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5227 NULL 17
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5312 NULL 18
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5315 NULL 19
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5322 NULL 20
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 589 NULL 21
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5905 NULL 22
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5903 NULL 23
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 5904 NULL 24
Retired 2004-05-24 00:00:00.000 2006-01-01 23:59:59.000 6041 NULL 25

(25 row(s) affected)

Since I'm only concerned with which SKUs exist in more than one promotion, I need the priority to be set for the promotion. Here's how I would like it to be displayed based on the insert statements below:

promoid startdate enddate skuid disclevel priority
--------- ----------------------- ----------------------- ------ ------------------- -----------
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 00:00:00.000 116 0.20000000000000001 1
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 00:00:00.000 555 0.20000000000000001 1
20%_2 2004-12-06 00:00:00.000 2006-01-01 00:00:00.000 116 0.20000000000000001 2
20%_2 2004-12-06 00:00:00.000 2006-01-01 00:00:00.000 555 0.20000000000000001 2
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 00:00:00.000 116 0.40000000000000002 3
Feb05Sale 2005-02-11 00:00:00.000 2005-02-19 00:00:00.000 555 0.40000000000000002 3

Here's the modified insert statements I previously posted:

insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'1'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'2'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'3'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'6'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'1'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'2'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'3'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'6'
,'116'
,60
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'1'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'2'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'3'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'4'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'6'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'7'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'8'
,'116'
,60
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'1'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'2'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'3'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('SepNov20%'
,'6'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'1'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'2'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'3'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('20%_2'
,'6'
,'555'
,220
,'.20')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'1'
,'555'
,220
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'2'
,'555'
,220
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'3'
,'555'
,220
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'4'
,'555'
,220
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'6'
,'555'
,220
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'7'
,'555'
,220
,'.40')
insert into
promolocation
(PromoID
,LocID
,SkuID
,PromoPrice
,DiscLevel)
values
('Feb05Sale'
,'8'
,'555'
,220
,'.40')

Thanks for all the help. It is greatly appreciated.

D

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-17 : 18:52:22

You're quite welcome. How's this?


declare @resultsWithPriority Table
	(PromoID	char(10)
	,priority	int identity(1,1))
	
insert @resultsWithPriority (promoid)
Select	p.PromoID
from	Promotion p
Where	enddate >= getdate()
order by EndDate desc
	,StartDate asc

Select	p.PromoID
	,StartDate
	,EndDate
	,skuid
	,max(disclevel) disclevel
	,t.priority
from	Promotion p
JOIN	Promolocation pl ON p.promoid = pl.promoid
JOIN	@resultsWithPriority t ON p.promoid = t.promoid
Group by p.PromoID
	,StartDate
	,EndDate
	,skuid
	,t.priority
order by EndDate desc
	,StartDate asc

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources