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

sub query from same table mysql

I have questions about sub query. I have table:

Emp_ID Date_Entry Clock_In Clock_Out
E-0001 2024-02-01 2024-02-01 22:01:00 NUll
E-0001 2024-02-02 NULL 2024-02-02 06:01:05
E-0001 2024-02-03 2024-02-03 06:01:00 2024-02-03 14:00:20
E-0001 2024-02-04 2024-02-04 08:00:00 2024-02-04 17:01:05

how to query with mysql so when the clock_in >= 22:00:00 then the clock_out is select the next day clock_out like below?:

Emp_ID Date_Entry Clock_In Clock_Out
E-0001 2024-02-01 2024-02-01 22:01:00 2024-02-02 06:01:05
E-0001 2024-02-02 NULL 2024-02-02 06:01:05
E-0001 2024-02-03 2024-02-03 06:01:00 2024-02-03 14:00:20
E-0001 2024-02-04 2024-02-04 08:00:00 2024-02-04 17:01:05

any help will be appreciated. thank you in advance!

Tags:

2

Answers


  1. 0 
    SELECT t1.Emp_ID, 
       t1.Date_Entry, 
       t1.Clock_In, 
                -- return next-day time if joined and is not NULL
                -- and current-day time if not joined
       COALESCE(t2.Clock_Out, t1.Clock_Out) Clock_Out
FROM table t1
                      -- join only when time is above 22:00
LEFT JOIN table t2 ON TIME(t1.Clock_In) >= '22:00'
                      -- join only next-day row (if exists)
                  AND t1.Date_Entry + INTERVAL 1 DAY = t2.Date_Entry
    Login or Signup to reply.
  2. 0

    If you’re working with MySQL 8, the window function LEAD() combined with CASE clause allows you to retrieve the next Clock_Out for cases where clock_in is 22:00:00 or later :

    SELECT Emp_ID, Date_Entry, Clock_In, COALESCE(next_Clock_In, Clock_Out)
FROM (
  SELECT *, 
       CASE WHEN TIME(Clock_In) >= '22:00' 
            THEN LEAD(Clock_Out) 
                 OVER (PARTITION BY Emp_ID ORDER BY Date_Entry) 
       END AS next_Clock_In
  FROM mytable
) AS s;

    Demo here

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

Back To Top
Search