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 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 |
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.
Can anyone help me with the query in mysql server?
This question seems to be related to this one :
We need first get Date_Time_In and Date_Time_Out using the conditional aggregation, and then, apply the LEAD() function whenever Date_Time_In > 22:00 :
WITH cte AS (
SELECT Employee, DATE(Date_Time) as Date_Time,
MAX(CASE WHEN Status = 'IN' THEN Date_Time END ) AS Date_Time_In,
MAX(CASE WHEN Status = 'OUT' THEN Date_Time END ) AS Date_Time_Out
FROM mytable
GROUP BY Employee, DATE(Date_Time)
),
cte2 AS (
SELECT *,
CASE WHEN TIME(Date_Time_In) >= '22:00'
THEN LEAD(Date_Time_Out) OVER (PARTITION BY Employee ORDER BY Date_Time)
END AS next_Date_Time_Out
FROM cte
)
SELECT Employee, Date_Time, Date_Time_In, COALESCE(next_Date_Time_Out, Date_Time_Out) as DateTime_Out
FROM cte2;