I’ve got a table:

For each employee_id and each date I need to find IN and OUT pairs.

If there’s pair IN and OUT return IN and OUT in pair-rows. In my example theres going to be one row for 02.01.2024 but two rows for 17.09.2024 and it means that employee left company in middle of the day.

There’s also case that he forgot to Clock in or clock out. Then I need to add proper description to new column.

What I want to achieve is basically something like that.

I’ve tried to do write query myself but failed.

That’s my code I tried:

WITH CTE_Start AS (
    SELECT 
        Data,
        Employee_id,
        Time,
        ROW_NUMBER() OVER (PARTITION BY Employee_id, Data ORDER BY Time) AS RW
    FROM 
        logs
    WHERE 
        Type = 'IN'
),
CTE_Stop AS (
    SELECT 
        Data,
        Employee_id,
        Time,
        ROW_NUMBER() OVER (PARTITION BY Employee_id, Data ORDER BY Time) AS RW
    FROM 
        logs
    WHERE 
        Type = 'OUT'
),
Base AS (
    SELECT 
        Data,
        Employee_id
    FROM 
        logs
    GROUP BY 
        Data,
        Employee_id
)
SELECT 
    p.Data,
    p.Employee_id,
    start.Time AS start,
    stop.Time AS stop,
    CASE 
        WHEN start.Time IS NOT NULL AND stop.Time IS NOT NULL THEN 
            DATEDIFF(SECOND, 
                     CAST(p.Data AS DATETIME) + CAST(start.Time AS DATETIME), 
                     CAST(p.Data AS DATETIME) + CAST(stop.Time AS DATETIME))
        ELSE 
            NULL
    END AS Praca_sekund,
    CASE 
        WHEN start.Time IS NOT NULL AND stop.Time IS NULL THEN 'Brak WYJŚCIE'
        WHEN start.Time IS NULL AND stop.Time IS NOT NULL THEN 'Brak WEJŚCIE'
        ELSE NULL
    END AS Praca_blad
FROM 
    Base p
LEFT JOIN 
    CTE_start start ON p.Data = start.Data AND p.Employee_id = start.Employee_id
LEFT JOIN 
    CTE_stop stop ON p.Data = stop.Data AND p.Employee_id = stop.Employee_id AND stop.RW = start.RW

There’s almost what I want to achieve but if there isn’t any IN type then it doesn’t return stop time because I make a join on row_number column.