selecting best match using group by and date range

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)

selecting best match using group by and date range

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-20 : 08:09:02

tom writes "I have a table 'Promotions'. It has one field for a primary key:
* promotionId uniqueidentifier

There is a unique index defined over 3 fields:
* [product_ref] uniqueidentifier
* [valid_from_date] datetime
* [valid_to_date] datetime

... and one other field
* [discount_percentage] money

For a given product, there could be any number of promotions defined for different date ranges. Two or more could also start on the same date or finish on the same date, but not both start and finish on the same date (as this would violate the unique index).

My task is to write a stored procedure that takes two parameters:
* @productId uniqueidentifier
* @purchaseDate datetime

and for the supplied product and point in time, returns only a single promotionId. The rules are:

* pick the promotion record for given product where the valid_from_date and the valid_to_date are closest to @purchaseDate.

For example, if product 'z' has two promotions running:
promo 1) starts 1Jan2005 ends 31Dec2005
promo 2) starts 25Dec2005 ends 10Jan2006
then:

if @purchaseDate = 24Dec2005, use promo1 (because it is the only range that contains 24Dec)

if @purchaseDate = 31Dec2005, use promo2 - because its start date is closest to @purchaseDate.

Furthermore, if there was a third promo that also started at the same time as promo 2:
promo 3) starts 25Dec2005 ends 5Jan2006
... then for a @purchaseDate of 31Dec, return promo 3 because although both the start dates of promo 2 and 3 are equally as close to @purchaseDate, the end_date of promo 3 is also closest to @purchaseDate.

All of the above has to be achieved using a single select statement, i.e. I cannot use cursors."

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-10-21 : 19:05:12

Well, I didn't want to use a single SELECT statement because that would've gotten pretty ugly fast, with the method that I used. So I used a couple of temp tables, but no cursors:


CREATE PROC GetPromotionId 
@purchaseDate	datetime,
@productid	int 
AS
SET NOCOUNT ON

SELECT promotionid, DATEDIFF(d, valid_from_date, @purchaseDate) FromDiff 
INTO #tmp_from_diffs
FROM Promotions 
WHERE valid_from_date <= @purchaseDate 
AND valid_to_date >= @purchaseDate 
AND product_ref = @productid 

SELECT C.promotionid, DATEDIFF(d, @purchaseDate, C.valid_to_date) ToDiff 
INTO #tmp_to_diffs 
FROM Promotions AS C 
JOIN	(SELECT B.promotionid 
	FROM	(SELECT MIN(FromDiff) MinFromDiff
		FROM #tmp_from_diffs) AS A 
	JOIN #tmp_from_diffs B 
	ON A.MinFromDiff = B.FromDiff) AS B 
ON C.promotionid = B.promotionid 
WHERE C.valid_from_date <= @purchaseDate 
AND C.valid_to_date >= @purchaseDate 
AND C.product_ref = @productid 

SELECT E.promotionid 
FROM	(SELECT MIN(ToDiff) AS MinToDiff 
	FROM #tmp_to_diffs) AS D 
JOIN #tmp_to_diffs AS E 
ON D.MinToDiff = E.ToDiff 

DROP TABLE #tmp_from_diffs
DROP TABLE #tmp_to_diffs

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-21 : 20:14:10

This should do what you want:


select
	top 1
	promotionId
from
	Promotions
where
	product_ref = @productId	and
	@purchaseDate between
		valid_from_date and valid_to_date
order by
	datediff(dd,valid_from_date,@purchaseDate),
	datediff(dd,@purchaseDate,valid_to_date)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources