Rank Sequential Groups by Date

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 Sequential Groups by Date

Author

Topic

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-05-04 : 13:48:28

Hi please help with this query:


declare @t1 table (billingky int, billdt datetime)
insert into @t1 values
(1,'2010-05-03'),
(2,'2010-05-03'),
(3,'2010-05-03'),
(4,'2010-05-04'),
(5,'2010-05-04'),
(6,'2010-05-03'),
(7,'2010-05-04'),
(8,'2010-05-04'),
(9,'2010-05-04'),
(10,'2010-05-03')

to illustrate my point:
1,'2010-05-03',1   --1-3 are group 1 for 2010-05-03
2,'2010-05-03',1
3,'2010-05-03',1
4,'2010-05-04',1   --4-5 are group 1 for 2010-05-04
5,'2010-05-04',1
6,'2010-05-03',2   --6 is group 2 for 2010-05-03
7,'2010-05-04',2   --7,8,9 are group 2 for 2010-05-04
8,'2010-05-04',2
9,'2010-05-04',2
10,'2010-05-03',3  --10 is group 3 for 2010-05-03


Desired result Output
Date       Group   Min   Max
2010-05-03 1       1     3
2010-05-03 2       6     6
2010-05-03 3       10    10
2010-05-04 1       4    5
2010-05-04 2       7    9

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 14:01:16

have a look at

http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-04 : 15:20:14

[code]SELECT
~~ROW_NUMBER() OVER (ORDER BY billdt, PartNum) AS [Group],~~
ROW_NUMBER() OVER (PARTITION BY billdt ORDER BY PartNum) AS [Group],
billdt AS Date,
MIN(billingky) AS Min,
MAX(billingky) AS MAX
FROM
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY billingky) -
ROW_NUMBER() OVER (PARTITION BY billdt ORDER BY billingky) AS PartNum
FROM
@t1
) AS T
GROUP BY
PartNum, billdt[/code] Opps I pasted the wrong one.. see edits.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-05-04 : 16:11:25

perfect, thanks Lamprey :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources