select first in and last out time - different date - 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-01  16:00  IN
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
200     2020-09-02  21:00  OUT

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 CTE AS(
SELECT CardID,
       [Date] AS DateIn,
       MIN(CASE TransactionCode WHEN 'In' THEN [time] ELSE '23:59:59.999' END) AS TimeIn, --'23:59:59.999' as we are after the MIN, and NULL is the lowest value
       [Date] AS DateOut,
       MAX(CASE TransactionCode WHEN 'Out' THEN [time] END) AS TimeOut
FROM YourTable
GROUP BY CardID, [Date])
SELECT C.DateIn,
   C.TimeIn,
   C.DateOut,
   C.TimeOut,
   DATEADD(MINUTE,DATEDIFF(MINUTE,C.TimeIn,C.TimeOut),CONVERT(time(0),'00:00:00')) AS Duration
FROM CTE C;

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

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

=====The Result Needed===== I want this result.

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

How to get the DateOUT and TimeOUT in the nextday? with the condition FIRST IN AND LAST OUT. Please help, thank you in advance.

Answer 1

This seems like you were really overly complicating the problem. Just use some conditional aggregation, and then get the difference in minutes:

WITH CTE AS(
    SELECT CardID,
           [Date] AS DateIn,
           MIN(CASE TransactionCode WHEN 'In' THEN [time] ELSE '23:59:59.999' END) AS TimeIn, --'23:59:59.999' as we are after the MIN, and NULL is the lowest value
           [Date] AS DateOut,
           MAX(CASE TransactionCode WHEN 'Out' THEN [time] END) AS TimeOut
    FROM YourTable
    GROUP BY CardID, [Date])
SELECT C.DateIn,
       C.TimeIn,
       C.DateOut,
       C.TimeOut,
       DATEADD(MINUTE,DATEDIFF(MINUTE,C.TimeIn,C.TimeOut),CONVERT(time(0),'00:00:00')) AS Duration
FROM CTE C;

This assumes that [date] is a date and [time] is a time (because, after all, that is what they are called...).

Side Note: it seems some what redundant have a DateIn and DateOut column when they will always have the same value. Might as well just have a [Date] Column.

---

Or perhaps, you are actually after this?

WITH CTE AS(
    SELECT CardID,
           [Date] AS DateIn,
           [Time] AS TimeIn,
           LEAD([Date]) OVER (PARTITION BY CardID ORDER BY [Date], [Time]) AS DateOut,
           LEAD([Time]) OVER (PARTITION BY CardID ORDER BY [Date], [Time]) AS TimeOut,
           TransactionCode
    FROM dbo.YourTable)
SELECT C.DateIn,
       C.TimeIn,
       C.DateOut,
       C.TimeOut
FROM CTE C
WHERE TransactionCode = 'IN';

Note that if that is the case, you would actually be better off storing the values [date] and [time] in a single column as a datetime/datetime2, not separate ones; as the values are clearly not distinct from each other.

---

Based on the (hopefully) final goal posts:

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,'20200901'),CONVERT(time(0),'16:00:00'),'IN'),
                (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'),
                (200,CONVERT(date,'20200902'),CONVERT(time(0),'21:00:00'),'OUT'))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';