Create view with working hours based on data

I've got a table:

Employee_id	Date	Time	Type
630	02.01.2024	09:45:50	IN
630	02.01.2024	17:02:40	OUT
630	13.02.2024	16:06:30	OUT
630	17.09.2024	06:37:50	IN
630	17.09.2024	08:07:30	OUT
630	17.09.2024	08:21:00	IN
630	17.09.2024	16:20:00	OUT

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.

Employee_id	Date	IN	OUT	DESCRIPTION
630	02.01.2024 00:00:00	09:45:50	17:02:40
630	13.02.2024 00:00:00	null	16:06:30	NOT CLOCKED IN
630	17.09.2024 00:00:00	06:37:50	08:07:30
630	17.09.2024 00:00:00	08:21:00	16:20:00

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.

Solution

It isn't clear exactly when you want to see in the date/time columns when the employee hasn't clocked out (maybe you want both IN rows?), but this should get you close, given this sample data:

CREATE TABLE #logs
(
  Employee_id int, 
  [Date]      date, 
  [Time]      time,
  [Type]      varchar(3)
);

INSERT #logs VALUES
(630, '20240102', '09:45:50', 'IN' ),
(630, '20240102', '17:02:40', 'OUT'),
(630, '20240213', '16:06:30', 'OUT'),
(630, '20240917', '06:37:50', 'IN' ),
(630, '20240917', '08:07:30', 'OUT'),
(630, '20240917', '08:21:00', 'IN' ),
(630, '20240917', '16:20:00', 'OUT'),
(630, '20240918', '09:44:00', 'IN' ),
(630, '20240918', '13:57:00', 'IN' );

A series of CTEs to avoid repetition as much as possible:

WITH sensible_datetime AS
(
  SELECT *, proper_time = CONVERT(datetime, 
            DATEADD(SECOND, DATEDIFF(SECOND, 0, time), 
            CONVERT(datetime, date)))
  FROM #logs
),
paired_with_previous AS 
(
  SELECT *, prev_time = LAG(proper_time, 1) OVER 
                        (PARTITION BY Employee_id ORDER BY proper_time),
            prev_type = LAG([Type], 1)      OVER 
                        (PARTITION BY Employee_id ORDER BY proper_time)
  FROM sensible_datetime
),
final AS 
(
  SELECT *, dir = CASE [Type] WHEN prev_type THEN [Type] END 
   FROM paired_with_previous 
  WHERE prev_time IS NOT NULL
    AND (([Type] = 'OUT' AND prev_type = 'IN') OR ([Type] = prev_type))
)
SELECT Employee_id, 
       [Date] = CONVERT(date,    
                CASE dir WHEN 'IN' THEN prev_time ELSE proper_time END),
       [In]   = CONVERT(time(0), 
                CASE dir WHEN 'OUT' THEN NULL ELSE prev_time END),
       [Out]  = CONVERT(time(0), 
                CASE dir WHEN 'IN'  THEN NULL ELSE proper_time END),
       [Description] = COALESCE('NOT CLOCKED ' + CASE dir WHEN 'IN' THEN 'OUT'
                                                 WHEN 'OUT' THEN 'IN' END, '')
  FROM final
 ORDER BY Employee_id, [Date], [In], [Out];

Output:

Employee_id	Date	IN	OUT	DESCRIPTION
630	2024-01-02	09:45:50	17:02:40
630	2024-02-13	NULL	16:06:30	NOT CLOCKED IN
630	2024-09-17	06:37:50	08:07:30
630	2024-09-17	08:21:00	16:20:00
630	2024-09-18	09:44:00	NULL	NOT CLOCKED OUT

SQL Fiddle