How do I find the difference between rows of datetimes-CodePudding

Here is my initial table

 ---------- ------------------------------ 
|   Event  |           occurred           |
 ---------- ------------------------------ 
|   Enter  |   2022-11-22 00:00:00.000    |
|   Exit   |   2022-11-22 02:00:00.000    |
|   Enter  |   2022-11-22 02:01:00.000    |
|   Exit   |   2022-11-22 05:00:00.000    |
 ---------- ------------------------------

Here are my expected results

 ----------------- -------------- 
|   Event         |  Time Spent  |
 ----------------- -------------- 
|   Inside        |   04:59:00   |
|   Outside       |   00:01:00   |
|   Total         |   05:00:00   |
 ----------------- --------------

What I want to do is to calculate the time spent inside, time spent outside, and the total time spent.

The logic that I can think of is to do a loop for each enter, then subtract it from the the row after it, then add it all together. How do that as a query?

CodePudding user response：

You could use a CTE to set up columns to group on, getting the previous value using LAG to do some math, and then finally get a total sum of the seconds and convert it to a time format. This assumes that the rows will always be in order of Enter > Exit > Enter > etc. Then just union the total on.

with parsed as
(
    select Event
        , DATEDIFF(SECOND, LAG(occurred, 1, occurred) over (order by occurred), occurred) seconds
        , case
            when Event = 'Exit' then 'Inside'
            else 'Outside'
          end place
    from tbl
)
select place Event
    , convert(time, dateadd(SS, sum(seconds), 0), 108) [Time Spent]
from parsed
group by place

union all

select 'Total' Event
    , convert(time, dateadd(SS, sum(seconds), 0), 108) [Time Spent]
from parsed