SQL - Group rows by contiguous date

Question

I have a table:

Value   Date 
100     01/01/2000
110     01/05/2002
100     01/10/2003
100     01/12/2004

I want to group the data in this way

Value       StartDate      EndDate
100         01/01/2000     30/04/2002
110         01/05/2002     30/09/2003
100         01/10/2003     NULL --> or value like '01/01/2099'

How can I accomplish this? Can a CTE be useful and how?

Answer 1

For RDBMS supported window functions (example on MS SQL database):

with Test(value, dt) as(
  select 100, cast('2000-01-01' as date) union all
  select 110, cast('2002-05-01' as date) union all  
  select 100, cast('2003-10-01' as date) union all
  select 100, cast('2004-12-01' as date)
)
select max(value) value, min(dt) startDate, max(end_dt) endDate
  from (
     select a.*, sum(brk) over(order by dt) grp
       from (
         select t.*,
                case when value!=lag(value) over(order by dt) then 1 else 0 end brk,
                DATEADD(DAY,-1,lead(dt,1,cast('2099-01-02' as date)) over(order by dt)) end_dt
          from Test t
      ) a
  ) b
 group by grp
 order by startDate

Answer 2

I think the difference of row numbers is simpler in this case:

select value, min(date) as endDate,
       dateadd(day, -1, lead(min(date)) over (order by min(date))) as endDate
from (select t.*,
             row_number() over (order by date) as seqnum,
             row_number() over (partition by value order by date) as seqnum_v
      from t
     ) t
group by value, (seqnum - seqnum_v);

The difference of the row numbers defines the groups you want. This is a bit hard to see at first . . . if you stare at the results of the subquery, you'll see how it works.