FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

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' );

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];

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

(9 rows affected)

(5 rows affected)