CODEWITHSUNDEEP
sqlsql-servert-sql
Simon
Simon

Reputation: 31

How do I calculate the amount of time between multiple datetimes in multiple rows in sql

I've done a search but I can't find any that are exactly what I need. I need to be able to calculate the amount of time that someone has been in the building over time in a sql query (T-SQL on SQL Server). The data looks like this:

UserId   Clocking        Status
------------------------------
1      01/12/2020 09:00   In
2      01/12/2020 09:12   In
1      01/12/2020 09:25   Out
3      01/12/2020 10:00   In
2      01/12/2020 10:45   Out
3      01/12/2020 13:11   Out 
1      03/12/2020 11:14   In
2      03/12/2020 15:56   In
1      03/12/2020 16:04   Out
2      03/12/2020 17:00   Out

I want the output to look like this:

UserId  TimeInBuilding
----------------------
1      03:35
2      05:25
3      03:11

Upvotes: 0

Views: 700

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Assuming that the ins/outs are perfectly interleaved, you can do this by assigning the next "out" time to the "in" time and aggregating:

select userid,
       sum(datediff(second, clocking, out_time)) / (60.0 * 60) as decimal_hours
from (select t.*,
             lead(clocking) over (partition by userid order by clocking) as out_time
      from t
     ) t
where status = 'In'
group by userid;

You can convert this to HH:MM format using:

select userid,
       convert(varchar(5),
               convert(time,
                       dateadd(second,
                               sum(datediff(second, clocking, out_time),
                               0)
                      )
               ) as hhmm
from (select t.*,
             lead(clocking) over (partition by userid order by clocking) as out_time
      from t
     ) t
where status = 'In'
group by userid;

Here is a db<>fiddle.

Upvotes: 1

Related Questions