CODEWITHSUNDEEP
sqlsql-server
Aparna
Aparna

Reputation: 845

SQL query to group when value changes

I have the following data in my sql db,

value                   timestamp
    0 ,   2018-03-21 14:32:24.417
    0 ,   2018-03-21 14:33:24.417
    0 ,   2018-03-21 14:34:24.417
    4 ,   2018-03-21 14:35:24.417
    4 ,   2018-03-21 14:36:24.417
    0 ,   2018-03-21 14:37:24.417
    0 ,   2018-03-21 14:38:24.417

I want the results to look as below

value,               min timestamp , duration of how long value was in this value
    0 ,    2018-03-21 14:32:24.417,     2
    4 ,    2018-03-21 14:35:24.417,     1
    0 ,    2018-03-21 14:37:24.417,     1

I tried using lead function, but since value repeats, it is not working. Any help will be greatly appreciated

Upvotes: 1

Views: 1020

Answers (1)

McNets
McNets

Reputation: 10827

On SQL-Server:

select value, min(timestamp), datediff(minute, min(timestamp), max(timestamp)) diff
from
    (select value, [timestamp], 
             sum(reset) over (order by timestamp) as grp
     from 
            (select value, [timestamp],
                    case when coalesce(lag(value) over (order by timestamp), '19000101') <> value then 1 end as reset
             from   tbl) t1
    ) t2
group by value, grp
GO

value | (No column name)    | diff
----: | :------------------ | ---:
    0 | 21/03/2018 14:32:24 |    2
    4 | 21/03/2018 14:35:24 |    1
    0 | 21/03/2018 14:37:24 |    1

dbfiddle here

Upvotes: 7

Related Questions