Calculate Duration based on a criteria in another column in SQL for specific IDs

Question

I have a table called Audit like below:

ID	Record	AccessedDTTM
16	Sign In	2021-03-23 04:41:33.770
16	Session End - Sign Out	2021-03-23 04:42:33.770
17	Sign In	2021-03-01 00:03:04.070
17	Session End - Sign Out	2021-03-01 00:33:52.717
18	Sign In	2021-03-01 01:31:00.527
18	Schedule	2021-03-01 01:31:31.407
18	Charge	2021-03-01 01:43:27.427
18	Session End - Sign Out	2021-03-01 01:47:27.940

I need to calculate the duration of the time accessed by the Users with Id 16 and 18. The duration should be based on their Record of Sign In and Session End - Sign Out entries.

I tried this query : select DateDiff(second, (Select AccessedDTTM from Audit Where Record = 'Sign In'), (Select AccessedDTTM from Audit Where Record = 'Session End - Sign Out')) as Duration Foom Audit Where ID In (16, 18)

But I am getting an error as below : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can someone please help me with the proper query to achieve this result? Thanks In Advance!

Answer 1

You can use conditional aggregation:

select id,
       datediff(second, 
                min(case when Record = 'Sign in' then AccessedDTTM end),
                max(case when Record = 'Session End - Sign Out' then AccessedDTTM end)
               ) as diff
from audit
where id in (16, 18)
group by id;

Making some reasonable assumptions about the data, you can simplify this to:

select id,
       datediff(second, min(AccessedDTTM), max(AccessedDTTM)
               ) as diff
from audit
where id in (16, 18) and
      record in ('Sign in', 'Session End - Sign Out')
group by id;