getting time duration from a single colum in sql server 2019

Question

Hi All,

I have the following test table with some same data

CREATE TABLE [dbo].Test ON [PRIMARY]
GO

INSERT INTO [Test] (
[EmpID],
[Date],
[Time],
[Client],
[Action]
)
VALUES
(
12029487,
'20210801',
'8:03:39' ,
'Web',
'Away'
),
(
12029487,
'20210801',
'8:04:09',
'Web',
'Available'
),
(
12029487,
'20210801',
'9:00:12',
'Web',
'Logout'
),
(
12029487,
'20210801',
'9:00:12',
'Web',
'Available'
),
(
12029487,
'20210801',
'9:53:48',
'Web',
'Logout'
);

The table should look like

What I am trying to accomplish is to get the time duration in a results column called 'Duration'. Where Action = 'Logout' I want to return a value of 0:00:00

Currently I am using an excel formula of =IF($E2="Logout",0,$C3-$C2) to return the values in 'Duration'.

The results should look like the image below

Any help will be greatly appreciated.

Answer 1

Check an approach:

;
with Q1 as
(
    select *, cast(date as datetime) + cast(time as datetime) as dt
    from Test
), 
Q2 as
(
    select *,
        case [Action]
        when 'Logout' then 0
        else datediff(second, dt, lead(dt) over (partition by EmpID order by dt))
        end as s
    from Q1
)
select EmpID, [Date], [Time], Client, [Action],
    concat( 
        format(s / 3600, '00'),
        ':',
        format((s - (s / 3600 * 3600)) / 60, '00'),
        ':',
        format(s - (s / 3600 * 3600) - ((s - (s / 3600 * 3600)) / 60 * 60), '00')
        ) as Duration
from Q2
order by EmpID, [Date], [Time]

It should work with durations that are greater than 24 hours. Probably it can be simplified.

Note that it is problematic to order the shown rows that contains the same date and time: 9:00:12. If your real table contains a primary key, for example, then you can add it to order by for lead.

Answer 2

Thanks Viorel-1 for your assistance.

This seems to be working well.

I tried the Lag function but wasn't aware of a Lead function.

Thanks and much appreciated.