Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – get next date record from table

I have 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 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? thank you!

Tags:

2

Answers


  1. 0

    Please:
    This query should work exactly as you need it:

    select 
    DATE_FORMAT(Date_Time,'%Y-%m-%d') as Date,
    Date_Time as DateTime_In,
    if(Status='IN' and Date_Time < concat(DATE_FORMAT(Date_Time,'%Y-%m-%d'),' 22:00:00'),
        (select Date_Time from tmp as tmpif1 where Status='Out' and DATE_FORMAT(tmpif1.Date_Time,'%Y-%m-%d')=DATE_FORMAT(tmp.Date_Time,'%Y-%m-%d')),
        (select Date_Time from tmp as tmpif2 where Status='Out' and DATE_FORMAT(tmpif2.Date_Time,'%Y-%m-%d')=DATE_FORMAT(DATE_ADD(tmp.Date_Time,INTERVAL 1 DAY),'%Y-%m-%d'))) as DateTime_Out
        
    from tmp
    where Status='IN';
    Login or Signup to reply.
  2. 0

    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;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search