CODEWITHSUNDEEP
sql-servert-sqldatewindow-functionsgaps-and-islands
Mason Younger
Mason Younger

Reputation: 63

SQL Server 2012 - Calculate time in state based on state change

I'm trying to calculate how long a machine was in a specific state then sum by hour. The state is only recorded on change so we can assume it was in the same state until changed.

I was trying to use partition, but I don't think that is the correct approach.

My table structure ordered desc:

+----------+-------------------------+
| state_id |         t_stamp         |
+----------+-------------------------+
|        0 | 2020-06-01 10:44:06.663 |
|        2 | 2020-06-01 10:43:56.660 |
|        0 | 2020-06-01 10:43:06.653 |
|        2 | 2020-06-01 10:42:56.653 |
|        0 | 2020-06-01 10:41:36.643 |
|        3 | 2020-06-01 10:41:26.640 |
|        0 | 2020-06-01 10:41:16.640 |
|        2 | 2020-06-01 10:40:56.637 |
|        0 | 2020-06-01 10:40:06.630 |
|        3 | 2020-06-01 10:39:56.630 |
+----------+-------------------------+

What I'm trying to get to:

+----------+------------------+
| state_id | duration_seconds |
+----------+------------------+
|        2 |               10 |
|        0 |               50 |
+----------+------------------+

Upvotes: 1

Views: 296

Answers (2)

saran3h
saran3h

Reputation: 14062

You can use Lead function.

select state_id, sum(elapsed) as duration_seconds
from (
select
    t_stamp,
    state_id, 
    lead(t_stamp) over(order by t_stamp) - t_stamp as elapsed 
from tablename
) t
group by state_id

Upvotes: 1

GMB
GMB

Reputation: 222622

You can use window functions, then aggregation:

select 
    state_id, 
    sum(datediff(second, t_stamp, lead_t_stamp)) duration_second
from  ( 
    select
        t.*,
        lead(t_stamp) over(order by t_stamp) lead_t_stamp
    from mytable t
) t
where lead_t_stamp is not null
group by state_id
order by state_id

This demo on DB Fiddle with your uample data returns:

state_id | duration_second
-------: | --------------:
       0 |             190
       2 |              40
       3 |              20

Upvotes: 2

Related Questions