Collapse multiple rows based on time values

user_14779877 0
Viewed 329

I'm trying to collapse rows with consecutive timeline within the same day into one row but having an issue because of gap in time. For example, my dataset looks like this.

Date            StartTime     EndTime      ID
2017-12-1       09:00:00      11:00:00    12345
2017-12-1       11:00:00      13:00:00    12345
2018-09-08      09:00:00      10:00:00    78465
2018-09-08      10:00:00      12:00:00    78465
2018-09-08      15:00:00      16:00:00    78465
2018-09-08      16:00:00      18:00:00    78465

As up can see, the first two rows can just be combined together without any issue because there's no time gap within that day. However. for the entries on 2019-09-08, there is a gap between 12:00 and 15:00. And I'd like to merge these four records into two different rows like this:

Date            StartTime     EndTime      ID
2017-12-1       09:00:00      13:00:00    12345
2018-09-08      09:00:00      12:00:00    78465
2018-09-08      15:00:00      18:00:00    78465

In other words, I only want to collapse the rows only when the time variables are consecutive within the same day for the same ID.

Could anyone please help me with this? I tried to generate unique group using LAG and LEAD functions but it didn't work.

sql-server sql
3 Answers

You can use a recursive cte. Group it as same group if the EndTime is same as next StartTime. And then find the MIN() and MAX()

with cte as
(
    select  rn = row_number() over (partition by [ID], [Date] order by [StartTime]),
            *
    from    tbl
),
rcte as
(
    -- anchor member
    select  rn, [ID], [Date], [StartTime], [EndTime], grp = 1
    from    cte
    where   rn  = 1

    union all

    -- recursive member
    select  c.rn, c.[ID], c.[Date], c.[StartTime], c.[EndTime],
            grp = case  when    r.[EndTime] = c.[StartTime] 
                        then    r.grp
                        else    r.grp + 1
                        end
    from    rcte r
            inner join cte c    on  r.[ID]      = c.[ID]    
                               and  r.[Date]    = c.[Date]
                               and  r.rn        = c.rn - 1
)
select  [ID], [Date], 
        min([StartTime]) as StartTime, 
        max([EndTime]) as EndTime
from    rcte
group by [ID], [Date], grp

db<>fiddle demo

user_1856470

Unless you have a particular objection to collapsing non-consecutive rows, which are consecutive for that ID, you can just use GROUP BY:

SELECT
    Date,
    StartTime = MIN(StartTime),
    EndTime = MAX(EndTime),
    ID
FROM table
GROUP BY ID, Date

Otherwise you can use a solution based on ROW_NUMBER:

SELECT
    Date,
    StartTime,
    EndTime,
    ID
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY Date, ID ORDER BY StartTime)
    FROM table
) t
WHERE rn = 1
user_148689970

This is an example of a gaps-and-islands problem -- actually a pretty simple example. The idea is to assign an "island" grouping to each row specifying that they should be combined because they overlap. Then aggregate.

How do you assign the island? In this case, look at the previous endtime and if it is different from the starttime, then the row starts a new island. Voila! A cumulative sum of the the start flag identifies each island.

As SQL:

select id, date, min(starttime), max(endtime)
from (select t.*,
             sum(case when prev_endtime = starttime then 0 else 1 end) over (partition by id, date order by starttime) as grp
      from (select t.*,
                   lag(endtime) over (partition by id, date order by starttime) as prev_endtime
            from t
           ) t
     ) t
group by id, date, grp;

Here is a db<>fiddle.

Note: This assumes that the time periods never span multiple days. The code can be very easily modified to handle that . . . but with a caveat. The start and end times should be stored as datetime (or a related timestamp) rather than separating the date and times into different columns. Why? SQL Server doesn't support '24:00:00' as a valid time.

user_11440350
Related