How to get a ranked result set for dates

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

Transact-SQL (2005)

How to get a ranked result set for dates

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 06:41:25

I have the following:

DECLARE @StartDate DATETIME
DELCARE @EndDate DATETIME
DECLARE @Levels INT

SET @StartDate = '20081128'
SET @EndDate = CURRENT_TIMESTAMP
SET @Levels = 5

-- Desired result set -------
Date Level
-----------------
20081128 1
20081129 2
20081130 3
20081201 4
20081202 5
20081203 1
20081204 2
20081205 3
20081206 4
20081207 5
20081208 1
20081209 2
20081210 3

How can I do this? Thanks

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 06:47:39

SELECT Date, (ROW_NUMBER() OVER (ORDER BY Date) - 1) % 5 + 1 AS Level
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 06:50:03

Thankyou. Works a treat!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 07:43:15

I have another twist. What if I wanted to specify the start number? Therefore, although the date is '20081201', the level for that date is not automatically 1?

See results below:

DECLARE @StartDate DATETIME
DELCARE @EndDate DATETIME
DECLARE @Levels INT

SET @StartDate = '20081201'
SET @EndDate = CURRENT_TIMESTAMP
SET @Levels = 5

Date Level
-----------------
20081201 4
20081202 5
20081203 1
20081204 2
20081205 3
20081206 4
20081207 5
20081208 1
20081209 2
20081210 3

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 09:17:41

[code]DECLARE @Sample TABLE
(
dt DATETIME
)

INSERT @Sample
SELECT '20081201' UNION ALL
SELECT '20081202' UNION ALL
SELECT '20081203' UNION ALL
SELECT '20081204' UNION ALL
SELECT '20081205' UNION ALL
SELECT '20081206' UNION ALL
SELECT '20081207' UNION ALL
SELECT '20081208' UNION ALL
SELECT '20081209' UNION ALL
SELECT '20081210'

DECLARE @StartValue INT,
@Levels INT

SELECT @StartValue = 4,
@Levels = 5

SELECT dt,
(ROW_NUMBER() OVER (ORDER BY dt) + @StartValue - 2) % @Levels + 1 AS [Level] -- minus one for row_number and minus one for @startvalue
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 09:45:24

Thanks Peso! You're a GENIUS!!!!!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 09:47:19

Everything is simple when you know your math.

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 09:56:04

Thats probably where I go wrong. I more art than maths! Ah well, thank god for you guys to do the hard bits for us

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 11:40:43

Another twist.

Currently, I'm getting (if I add an ORDER DESC clause at the end):

10 Dec 2008 5
09 Dec 2008 4
08 Dec 2008 3
07 Dec 2008 2
06 Dec 2008 1
05 Dec 2008 5
04 Dec 2008 4
03 Dec 2008 3
02 Dec 2008 2
01 Dec 2008 1
30 Nov 2008 5
29 Nov 2008 4
28 Nov 2008 3

But what I want is:

10 Dec 2008 3
09 Dec 2008 2
08 Dec 2008 1
07 Dec 2008 5
06 Dec 2008 4
05 Dec 2008 3
04 Dec 2008 2
03 Dec 2008 1
02 Dec 2008 5
01 Dec 2008 4
30 Nov 2008 3
29 Nov 2008 2
28 Nov 2008 1

Any more great advice?

So

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 12:03:24

Begin with ORDER BY dt DESC in the ROW_NUMBER() function...

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 12:05:10

[code]SELECT dt,
(ROW_NUMBER() OVER (ORDER BY dt)-1) % 5 + 1 AS [Value]
FROM @Sample[/code]

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 12:08:56

Now I get this:

10 Dec 2008 3
09 Dec 2008 4
08 Dec 2008 5
07 Dec 2008 1
06 Dec 2008 2
05 Dec 2008 3
04 Dec 2008 4
03 Dec 2008 5
02 Dec 2008 1
01 Dec 2008 2
30 Nov 2008 3
29 Nov 2008 4
28 Nov 2008 5

Close, but still not quite right? I have a work around, but is it possible using the function you suggested?

10 Dec 2008 3
09 Dec 2008 2
08 Dec 2008 1
07 Dec 2008 5
06 Dec 2008 4
05 Dec 2008 3
04 Dec 2008 2
03 Dec 2008 1

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-10 : 12:27:55

I take it back, I don't have a work around