Query to merge continuous temporal records



I have a table like this:

id     START_DATE   end_date
1      01/01/2011   01/10/2011
2      01/11/2011   01/20/2011
3      01/25/2011   02/01/2011
4      02/10/2011   02/15/2011
5      02/16/2011   02/27/2011

I want to merge the records where the start_date is just next day of end_date of another record: So the end record should be something like this:

new_id     START_DATE   end_date
1         01/01/2011   01/20/2011
2         01/25/2011   02/01/2011
3         02/10/2011   02/27/2011

One way that I know to do this will be to create a row based temp table with various rows as dates (each record for one date, between the total range of days) and thus making the table flat.

But there has to be a cleaner way to do this in a single query… e.g. something using row_num?

Thanks guys.





declare @T table
  id int,
  start_date datetime,
  end_date datetime

insert into @T values
(1,      '01/01/2011',   '01/10/2011'),
(2,      '01/11/2011',   '01/20/2011'),
(3,      '01/25/2011',   '02/01/2011'),
(4,      '02/10/2011',   '02/15/2011'),
(5,      '02/16/2011',   '02/27/2011')

select row_number() over(order by min(dt)) as new_id,
       min(dt) as start_date,
       max(dt) as end_date
from (
      select dateadd(day, N.Number, start_date) as dt,
             dateadd(day, N.Number - row_number() over(order by dateadd(day, N.Number, start_date)), start_date) as grp
      from @T
        inner join master..spt_values as N
          on N.number between 0 and datediff(day, start_date, end_date) and
             N.type = 'P'
     ) as T
group by grp
order by new_id        

You can use a numbers table instead of using master..spt_values.


Facebook Comments

Post a comment