CODEWITHSUNDEEP
t-sqlsql-server-2008-r2rangegroupingranking
mooselover
mooselover

Reputation: 5

TSQL advanced ranking, grouping to find date spans

I need to do some advanced grouping in TSQL with data that looks like this:

PK YEARMO DATA
1 201201 AAA
1 201202 AAA
1 201203 AAA
1 201204 AAA
1 201205 (null)
1 201206 BBB
1 201207 AAA
2 201301 CCC
2 201302 CCC
2 201303 CCC
2 201304 DDD
2 201305 DDD

And then, every time DATA changes per primary key, pull up the date range for said item so that it looks something like this:

PK  START_DT  STOP_DT  DATA
1  201201  201204  AAA
1  201205  201205  (null)
1  201206  201206  BBB
1  201207  201207  AAA
2  201301  201303  CCC
2  201304  201305  DDD

I've been playing around with ranking functions but haven't had much success. Any pointers in the right direction would be supremely awesome and appreciated.

Upvotes: 0

Views: 185

Answers (1)

jpw
jpw

Reputation: 44891

You can use the row_number()function to partition your data into ranges:

SELECT 
    PK, 
    START_DT = MIN(YEARMO),
    STOP_DT  = MAX(YEARMO),
    DATA
FROM (
    SELECT 
       PK, DATA, YEARMO,
       ROW_NUMBER() OVER (ORDER BY YEARMO) -
       ROW_NUMBER() OVER (PARTITION BY PK, DATA ORDER BY YEARMO) grp
    FROM your_table
    ) A
GROUP BY PK, DATA, grp
ORDER BY MIN(YEARMO)

Sample SQL Fiddle

Upvotes: 2

Related Questions