How to find date ranges in records with consecutive dates and duplicate data

Posted by alan s on Stack Overflow See other posts from Stack Overflow or by alan s
Published on 2010-05-15T07:03:56Z Indexed on 2010/05/15 7:14 UTC
Read the original article Hit count: 246

Filed under:
|
|

There's probably any easy solution for this, but I can't see it. I have a table with consecutive dates and often duplicate associated data for several of these consecutive dates:

Date       Col1  Col2
5/13/2010  1     A
5/14/2010  1     A
5/15/2010  2     B
5/16/2010  1     A
5/17/2010  1     A
5/18/2010  3     C
5/19/2010  3     C
5/20/2010  3     C

Using MS T-SQL, I wish to find the start and end dates for each run of distinct Col1 and Col2 values:

StartDate  EndDate    Col1  Col2
5/13/2010  5/14/2010  1     A
5/15/2010  5/15/2010  2     B
5/16/2010  5/17/2010  1     A
5/18/2010  5/20/2010  3     C

Assumptions: There are never any missing dates. Col1 and Col2 are not null. Any ideas - preferably that don't use cursors? Many thanks, -alan

© Stack Overflow or respective owner

Related posts about tsql

Related posts about database