CODEWITHSUNDEEP
sqlsql-serverdatetimeaggregate-functionsgaps-and-islands
Plaidpenguinhat
Plaidpenguinhat

Reputation: 79

Condense multiple consecutive rows using first and last row

I'm trying to find a way to condense consecutive similar records into 1 row, for example:

Status    starttime                 endtime
State1    2020-11-01 13:00:29.000   2020-11-01 13:03:59.000
State1    2020-11-01 13:03:59.000   2020-11-01 13:04:01.000
State1    2020-11-01 13:04:01.000   2020-11-01 13:05:27.000
State1    2020-11-01 13:05:27.000   2020-11-01 13:05:29.000
State2    2020-11-01 13:05:29.000   2020-11-01 13:11:31.000
State2    2020-11-01 16:19:35.000   2020-11-01 16:19:55.000

would condense to

Status      starttime                 endtime
State1      2020-11-01 13:00:29.000   2020-11-01 13:05:29.000
State2      2020-11-01 13:05:29.000   2020-11-01 13:11:31.000
State2      2020-11-01 16:19:35.000   2020-11-01 16:19:55.000

In this case, the first 4 rows have been condensed because they are the same state, and are consecutive times. The last 2 rows are not condensed, because there is a gap in the time between them.

Is this possible?

Upvotes: 0

Views: 112

Answers (1)

GMB
GMB

Reputation: 222542

This is a gaps and islands problem, where you want to group together consecutive rows that have the same status and adjacent periods.

You can use window functions; the idea is to define groups with a window sum that increments whenever there is a status change or a periods break:

select min(status) as status, min(starttime) as starttime, max(endtime) as endtime
from (
    select t.*,
        sum(case when starttime = lag_endtime and status = lag_status then 0 else 1 end) over(order by starttime) as grp
    from (
        select t.*,
            lag(endtime) over(order by starttime) lag_endtime,
            lag(status)  over(order by starttime) lag_status
        from mytable t
    ) t
) t
group by grp

Demo on DB Fiddle:

status | starttime               | endtime                
:----- | :---------------------- | :----------------------
State1 | 2020-11-01 13:00:29.000 | 2020-11-01 13:05:29.000
State2 | 2020-11-01 13:05:29.000 | 2020-11-01 13:11:31.000
State2 | 2020-11-01 16:19:35.000 | 2020-11-01 16:19:55.000

Upvotes: 1

Related Questions