Query to merge continuous temporal records

Question

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.

Charles Bretana · Answer 1 · 2011-09-22T20:11:43.493

Try This

  Declare @chgRecs Table 
      (updId int primary key not null,
       delId int not null,
       endt datetime not null)
  While Exists (Select * from Table a
                Where Exists 
                    (Select * from table
                     Where start_date = 
                         DateAdd(day, 1, a.End_Date))) 
  Begin
      Insert @chgRecs (updId, delId , endt)
      Select a.id, b.id, b.End_Date, 
      From table a 
      Where Exists 
          (Select * from table
           Where start_date = 
              DateAdd(day, 1, a.End_Date)))
         And Not Exists
             (Select * from table
              Where end_Date = 
                 DateAdd(day, -1, a.Start_Date)))

      Delete table Where id In (Select delId from @chgRecs ) 
      Update table set
          End_Date = u.endt
      From table t join @chgRecs u 
          On u.updId = t.Id
      Delete @delRecs
  End

The multi-part identifier "b.id" could not be bound. – Tintin Sep 22 '11 at 20:43 — Tintin, Sep 22 '11 at 20:43

score 0 · Answer 2 · answered Sep 22 '11 at 20:18

No, was not looking for a loop...

I guess this is a good solution:

taking all the data in a #temp table

SELECT * FROM #temp
SELECT t2.start_date , t1.end_date FROM #temp t1 JOIN #temp t2 ON t1.start_date = DATEADD(DAY,1,t2.end_date)
UNION
SELECT START_DATE,end_date FROM #temp WHERE start_date NOT IN  (SELECT t2.START_DATE FROM #temp t1 JOIN #temp t2 ON t1.start_date = DATEADD(DAY,1,t2.end_date))
AND end_date NOT IN (SELECT t1.end_Date FROM #temp t1 JOIN #temp t2 ON t1.start_date = DATEADD(DAY,1,t2.end_date))
DROP TABLE #temp

Please let me know if there is anything better than this.

Thanks guys.

But this works to merge only two rows at a time... therefore this will not work... will try what Charles has suggested (although I did not want to use loops)... — Tintin, Sep 22 '11 at 20:40

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

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.

edited May 23 '17 at 12:27

Community

1
1

answered Sep 22 '11 at 20:46

Mikael Eriksson

136,425
22
210
281

This worked like charm. Just one question: what is master..spt_values? – Tintin Sep 22 '11 at 21:00
@user503510 - It's a system table in SQL Server. http://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-mea – Mikael Eriksson Sep 22 '11 at 21:02
@user503510 - Max number in master..spt_values is 2048 so if you expect to have a continuous range of dates that spans more than that you need a numbers table instead. – Mikael Eriksson Sep 22 '11 at 21:05

score 0 · Answer 4 · answered Sep 22 '11 at 21:25

A recursive solution:

CREATE TABLE TestData
(
  Id INT PRIMARY KEY,
  StartDate DATETIME NOT NULL,
  EndDate DATETIME NOT NULL
);
SET DATEFORMAT MDY;
INSERT  TestData 
SELECT  1,      '01/01/2011',   '01/10/2011'
UNION ALL
SELECT  2,      '01/11/2011',   '01/20/2011'
UNION ALL
SELECT  3,      '01/25/2011',   '02/01/2011'
UNION ALL
SELECT  4,      '02/10/2011',   '02/15/2011'
UNION ALL
SELECT  5,      '02/16/2011',   '02/27/2011'
UNION ALL
SELECT  6,      '02/28/2011',   '03/06/2011'
UNION ALL
SELECT  7,      '02/28/2011',   '03/03/2011'
UNION ALL
SELECT  8,      '03/10/2011',   '03/18/2011'
UNION ALL
SELECT  9,      '03/19/2011',   '03/25/2011';

WITH RecursiveCTE
AS
(
    SELECT  t.Id, t.StartDate, t.EndDate
            ,1 AS GroupID
    FROM    TestData t
    WHERE   t.Id=1
    UNION ALL
    SELECT  crt.Id, crt.StartDate, crt.EndDate
            ,CASE WHEN DATEDIFF(DAY,prev.EndDate,crt.StartDate)=1 THEN prev.GroupID ELSE prev.GroupID+1 END
    FROM    TestData crt
    JOIN    RecursiveCTE prev ON crt.Id-1=prev.Id
    --WHERE crt.Id > 1  
)
SELECT  cte.GroupID, MIN(cte.StartDate) AS StartDate, MAX(cte.EndDate) AS EndDate
FROM    RecursiveCTE cte
GROUP BY cte.GroupID
ORDER BY cte.GroupID;

DROP TABLE TestData;

Query to merge continuous temporal records

4 Answers4

Linked