Select first in and last out time - different date and null condition - from data finger

Question

Here is my data finger table [dbo].[tFPLog]

CardID  Date        Time   TransactionCode
100     2020-09-01  08:00  IN
100     2020-09-01  17:00  OUT 
100     2020-09-01  17:10  OUT 
200     2020-09-02  02:00  OUT
200     2020-09-02  02:15  OUT
100     2020-09-02  07:00  IN
100     2020-09-02  16:00  OUT
200     2020-09-02  09:55  IN
200     2020-09-02  10:00  IN

Conditions

1. Assume Employees will be IN and OUT in same day/next day.
2. Assume There will be multiple IN and OUT for same day/next day for employees. So need first IN and Last Out.
3. Duration = (FirstInTime - LastOutTime)

The current result I get using the query:

WITH VTE AS(
    SELECT *
    FROM (VALUES(100,CONVERT(date,'20200901'),CONVERT(time(0),'08:00:00'),'IN'),
                (100,CONVERT(date,'20200901'),CONVERT(time(0),'17:00:00'),'OUT'),
                (100,CONVERT(date,'20200901'),CONVERT(time(0),'17:10:00'),'OUT'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'02:00:00'),'OUT'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'02:15:00'),'OUT'),
                (100,CONVERT(date,'20200902'),CONVERT(time(0),'07:00:00'),'IN'),
                (100,CONVERT(date,'20200902'),CONVERT(time(0),'16:00:00'),'OUT'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'09:55:00'),'IN'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'10:00:00'),'IN'))V(CardID,[Date],[Time],TransactionCode)),
Changes AS(
    SELECT CardID,
           DATEADD(MINUTE,DATEDIFF(MINUTE, '00:00:00',[time]),CONVERT(datetime2(0),[date])) AS Dt2, --Way easier to work with later
           TransactionCode,
           CASE TransactionCode WHEN LEAD(TransactionCode) OVER (PARTITION BY CardID ORDER BY [Date],[Time]) THEN 0 ELSE 1 END AS CodeChange
    FROM VTE V),
Groups AS(
    SELECT CardID,
           dt2,
           TransactionCode,
           ISNULL(SUM(CodeChange) OVER (PARTITION BY CardID ORDER BY dt2 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS Grp
    FROM Changes),
MinMax AS(
    SELECT CardID,
           TransactionCode,
           CASE TransactionCode WHEN 'IN' THEN MIN(dt2) WHEN 'Out' THEN MAX(dt2) END AS GrpDt2
    FROM Groups
    GROUP BY CardID,
             TransactionCode,
             Grp),
--And now original Logic
CTE AS(
    SELECT CardID,
           GrpDt2 AS DatetimeIn,
           LEAD([GrpDt2]) OVER (PARTITION BY CardID ORDER BY GrpDt2) AS DateTimeOut,
           TransactionCode
    FROM MinMax)
SELECT C.CardID,
       CONVERT(date,DatetimeIn) AS DateIn,
       CONVERT(time(0),DatetimeIn) AS TimeIn,
       CONVERT(date,DatetimeOut) AS DateOtt,
       CONVERT(time(0),DatetimeOut) AS TimeOut,
       DATEADD(MINUTE, DATEDIFF(MINUTE,DatetimeIn, DateTimeOut), CONVERT(time(0),'00:00:00')) AS Duration
FROM CTE C
WHERE TransactionCode = 'IN';

=====The Current Result======

CardID  DateIN      TimeIN  DateOUT     TimeOUT  Duration
100     2020-09-01  08:00   2020-09-01  17:10    09:10
100     2020-09-02  07:00   2020-09-02  16:00    09:00
200     2020-09-02  09:55   NULL        NULL     NULL

=====The Result Needed======

CardID  DateIN      TimeIN  DateOUT     TimeOUT  Duration
100     2020-09-01  08:00   2020-09-01  17:10    09:10
100     2020-09-02  07:00   2020-09-02  16:00    09:00
200     NULL        NULL    2020-09-02  02:15    NULL
200     2020-09-02  09:55   NULL        NULL     NULL

How to get the NULL Value For the Date IN and TimeIN? With the condition FIRST IN AND LAST OUT. Please help, thank you in advance.

Answer 1

This is a gaps-and-islands problem. Here is an approach using window functions:

select card_id, 
    min(case when transaction_code = 'IN' then dt end) dt_in,
    max(case when transaction_code = 'OUT' then dt end) dt_out
from (
    select t.*,
        sum(case when transaction_code = 'IN' and (lag_transaction_code is null or lag_transaction_code <> 'IN') then 1 else 0 end)
            over(partition by card_id order by dt) grp
    from (
        select t.*,
            lag(transaction_code) over(partition by card_id order by dt) lag_transaction_code
        from (
            select t.*, cast(date as datetime) + cast(time as datetime) dt
            from vte t
        ) t
    ) t
) t
group by card_id, grp
order by card_id, dt_in

The idea is to identify the first "IN"s (using lag() and a window sum()) and to use that to build groups of adjacent records. Then we can use conditional aggregation to retrieve the corresponding bounds each range.

Note that you should not be storing date date and time components in two different columns - this makes things more complicated, for no obvious benefit. I added another level of nesting to generate proper datetimes.

Demo on DB Fiddle:

card_id | dt_in                   | dt_out                 
------: | :---------------------- | :----------------------
    100 | 2020-09-01 08:00:00.000 | 2020-09-01 17:10:00.000
    100 | 2020-09-02 07:00:00.000 | 2020-09-02 16:00:00.000
    200 | null                    | 2020-09-02 02:15:00.000
    200 | 2020-09-02 09:55:00.000 | null