I have a table in which I stored in and out time as separate rows I am looking for a way to select in time and out time as two columns so I can count the total time spent based on in and out. so I have table like this

Time                          TimeType
2022-04-04 09:13:19.000         IN
2022-04-04 09:20:54.000         OUT
2022-04-04 09:21:54.000         IN
2022-04-04 09:25:54.000         OUT
2022-04-04 09:26:54.000         IN
2022-04-04 09:28:54.000         IN

I want to select and show as:

inTime                          outTime                   timeSpent
2022-04-04 09:13:19.000         2022-04-04 09:20:54.000    7
2022-04-04 09:21:54.000         2022-04-04 09:25:54.000    4
2022-04-04 09:26:54.000         NULL                       0

The NULL means it's error so any null values should be ignored Please let me know if there is any way to select them as above. Thanks

I tried like this:

SELECT (SELECT Times AS inTime FROM Table WHERE Times>='2022-09-29 00:00:00' AND Times<'2022-09-29 23:59:59' AND timeType='IN' AND personID='1'),
(SELECT Times AS outTime FROM Table WHERE Times>='2022-09-29 00:00:00' AND Times<'2022-09-29 23:59:59' AND timeType='OUT' AND personID='1')

but the above games be error because I can't select multiple rows.

then I tried join like this:

SELECT A.times AS inTime, B.times AS outTime FROM Table A
INNER JOIN Table B ON A.personID=B.personID
WHERE A.Times>='2022-04-04 00:00:00' AND A.Times<'2022-04-04 23:59:59' AND A.timeType='IN' AND A.personID='1' AND B.Times>='2022-04-04 00:00:00' AND B.Times<'2022-04-04 23:59:59' AND B.timeType='IN' AND B.personID='1' 

the above join is repeating inTime and outtimes

CodePudding user response：

So you can just take advantage of the LEAD analytic function to check the 'TimeType' from the next row to see if its a valid row (i.e. OUT follows IN) and if it is valid use LEAD to take the Time value from the next row and then calculate the difference. Finally filter out only IN rows.

declare @MyTable table ([Time] datetime2(3), TimeType varchar(3));

insert into @MyTable ([Time], TimeType)
values
('2022-04-03 09:13:19.000', 'IN'),
('2022-04-04 09:20:54.000', 'OUT'),
('2022-04-04 09:21:54.000', 'IN'),
('2022-04-04 09:25:54.000', 'OUT'),
('2022-04-04 09:26:54.000', 'IN'),
('2022-04-04 09:28:54.000', 'IN');

with cte as (
    select *
        , lead(TimeType) over (order by [Time] asc) LeadTimeType
        , lead([Time]) over (order by [Time] asc) OutTime
    from @MyTable
)
select [Time] InTime, OutTime
    , dateadd(second, datediff(second, [Time], OutTime), convert(time(3),'00:00:00.000')) TimeSpent
from cte
-- Either the IN row has a matching OUT row, or its the last 'IN' row
where (LeadTimeType != TimeType or LeadTimeType is null) and TimeType = 'IN'
order by [Time] asc;

Returns:

Note that adding your sample data as DDL DML (as shown here) makes it much easier for people to assist.