attendance table log mysql

Question

I have an attendance table like this:

Employee	Date_Time	Status
std001	2024-02-01 22:01:00	IN
std001	2024-02-02 06:00:05	OUT
std001	2024-02-03 07:59:00	IN
std001	2024-02-03 17:00:00	OUT
std001	2024-02-04 06:00:00	IN
std001	2024-02-04 14:00:00	OUT

I want to select from the table above with date range (ex. 2024-02-01 to 2024-02-05 to get result like this below:

Employee	Date	DateTime_In	DateTime_Out
std001	2024-02-01	2024-02-01 22:01:00	2024-02-02 06:00:05
std001	2024-02-02	NULL	2024-02-02 06:00:05
std001	2024-02-03	2024-02-03 07:59:00	2024-02-03 17:00:00
std001	2024-02-04	2024-02-04 06:00:00	2024-02-04 14:00:00
std001	2024-02-05	NULL	NULL

The "DateTime_In" is from the "Date_Time" with status "IN", and if the "DateTime_In" is < 22:00:00 then the "DateTime_Out" is the "Date_Time" with status "OUT" from same date. But if the "DateTime_In" >= 22:00:00 then the "DateTime_Out" is the "Date_Time" with Status "OUT" from the next day. but if there are no "DateTime_In" and "DateTime_Out" then it show null on corresponding date.

Can anyone help me with the query in mysql server?

Row

std001  2024-02-02  NULL    2024-02-02 06:00:05 seems can be 
std001  2024-02-02  NULL    NULL . 
And/Or std001   2024-02-01  2024-02-01 22:01:00 2024-02-02 06:00:05 
--> std001  2024-02-02  2024-02-01 22:01:00 2024-02-02 06:00:05

Answer 1

The Date for the fifth makes no sense as it is not in the Table.

The Query can be improved to check if the LAG(status) is IN if two OUT can happen, but then the logic would be even more complicated

CREATE TABLE tab (
  `Employee` VARCHAR(6),
  `Date_Time` DATETIME,
  `Status` VARCHAR(3)
);

INSERT INTO tab
  (`Employee`, `Date_Time`, `Status`)
VALUES
  ('std001', '2024-02-01 22:01:00', 'IN'),
  ('std001', '2024-02-02 06:00:05', 'OUT'),
  ('std001', '2024-02-03 07:59:00', 'IN'),
  ('std001', '2024-02-03 17:00:00', 'OUT'),
  ('std001', '2024-02-04 06:00:00', 'IN'),
  ('std001', '2024-02-04 14:00:00', 'OUT');

Records: 6  Duplicates: 0  Warnings: 0

WITH CTE AS (
SELECT
  `Employee`,
  DATE(`Date_Time`) `date`,
CASE WHEN `Status` = 'IN'  THEN `Date_Time` END `DateTime_In`,
CASE WHEN LEAD(`Status`) OVER (PARTITION BY `Employee` ORDER BY `Date_Time` ) = 'OUT'  
  THEN LEAD(`Date_Time`) OVER (PARTITION BY `Employee` ORDER BY `Date_Time` ) 
  ELSE 
  CASE WHEN DATE(LAG(`Date_Time`) OVER (PARTITION BY `Employee` ORDER BY `Date_Time` )) =  DATE(`Date_Time`)
     THEN NULL ELSE `Date_Time` END 
  END 'DateTime_Out'
FROM tab)

SELECT * FROM CTE WHERE `DateTime_Out` IS NOT NULL

Employee	date	DateTime_In	DateTime_Out
std001	2024-02-01	2024-02-01 22:01:00	2024-02-02 06:00:05
std001	2024-02-02	null	2024-02-02 06:00:05
std001	2024-02-03	2024-02-03 07:59:00	2024-02-03 17:00:00
std001	2024-02-04	2024-02-04 06:00:00	2024-02-04 14:00:00

fiddle