Rank is making me see double - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Rank is making me see double

Author

Topic

hairlessmonkey
Starting Member

4 Posts

Posted - 2012-05-03 : 14:58:04

I'm sort of new to this and a fast learner, but I got stuck trying to figure this out.

Basically I am trying to use rank to get the current promotional price of an itemID by colorType based on several date factors.
1. The enddate can't be expired
2. If there is no start date then the Price with the most recent PromoCreated date is used

I gave a generic example of what I have. The lines with current are the prices that should be used. Can someone please help me figure this out?

itemID|startdate |enddate |salePrice|ColorType|PromoCreated |CurrentPrice
333444| 5/ 1/2012|5/3/2012 | 66 | 1 | 5/ 2/12 11:52 AM
333444| 4/30/2012| | 86 | 1 | 4/30/12 12:20 PM current
512358| | 690.56 | 2 | 1/17/11 1:41 PM current
512358| | 810.56 | 2 | 1/17/11 1:40 PM
111222| 5/ 7/2012|5/14/2012| 570 | 1 | 5/2/12 11:55 AM current
111222| 4/30/2012| | 670 | 1 | 4/30/12 12:28 PM

12345 | NULL |NULL |408.75 | 1 | 4/25/10 8:47 PM current
67890 | NULL |NULL |18.99 | 2 | 3/17/11 6:18 PM current
111213| 10/25/2011|NULL |19.9 | 2 | 10/25/11 12:54 PM current

the pony rides again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:03:17

so what should be the output? if you just want current ones is it matter of adding a filter

WHERE CurrentPrice='current'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hairlessmonkey
Starting Member

4 Posts

Posted - 2012-05-03 : 15:20:27

quote:
Originally posted by visakh16

so what should be the output? if you just want current ones is it matter of adding a filter

WHERE CurrentPrice='current'?

Sorry, I should have been more specific. Current isn't generated. That's my manual representation of the items I want in the sample set.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

the pony rides again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:28:56

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY itemID ORDER BY CASE WHEN enddate > GETDATE() THEN 0 ELSE 1 END ASC,COALESCE(startdate,PromoCreated) DESC) AS Rn,*
FROM table
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hairlessmonkey
Starting Member

4 Posts

Posted - 2012-05-03 : 16:06:32

I forgot to add one more data set that occurs:

888999|11/29/11|NULL |66|1|5/2/12 11:52 AM
888999|NULL|NULL|86|2|4/30/12 12:20 PM

In this case, the rank would say the 1st line is 1 and the second it 2. But since the colortypes are different it should be 1 and 1.

quote:
Originally posted by visakh16

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY itemID ORDER BY CASE WHEN enddate > GETDATE() THEN 0 ELSE 1 END ASC,COALESCE(startdate,PromoCreated) DESC) AS Rn,*
FROM table
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

the pony rides again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 16:13:49

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY itemID,ColorType
ORDER BY CASE WHEN enddate > GETDATE()
THEN 0
ELSE 1
END ASC,
COALESCE(startdate,PromoCreated) DESC) AS Rn,*
FROM table
)t
WHERE Rn=1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hairlessmonkey
Starting Member

4 Posts

Posted - 2012-05-03 : 16:43:33

Yea! It works! I couldn't find the right words to figure out a conditioned partition and didn't know about COALESCE. I can't believe how fast you were about this. Thank you SOOOO much!

quote:
Originally posted by visakh16

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY itemID,ColorType
ORDER BY CASE WHEN enddate > GETDATE()
THEN 0
ELSE 1
END ASC,
COALESCE(startdate,PromoCreated) DESC) AS Rn,*
FROM table
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

the pony rides again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 17:16:51

Welcome

learn about PARTITION BY here

http://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources