CODEWITHSUNDEEP
sqlsql-server
AbbathCL
AbbathCL

Reputation: 99

There is a way to select the time elapsed in SQL Server

I have an example table (don't mind the column names)

enter image description here

I want to find a effective way to select the elapsed time between value is 1 and 0 as this: 2021-02-15 12:32:47.000-2021-02-15 13:33:08.000= 1 hour approx, and so on.

thanks in advance with a method to do so.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Assuming they are interleaved, you can use lead():

select t.*,
       datediff(second, date, next_date) / (60.0 * 60) as decimal_hours
from (select t.*,
             lead(date) over (order by date) as next_date
      from t
     ) t
where value = 1;

If you want the total, use aggregation:

select sum(datediff(second, date, next_date) / (60.0 * 60)) as decimal_hours
from (select t.*,
             lead(date) over (order by date) as next_date
      from t
     ) t
where value = 1;

Upvotes: 1

Related Questions