Mysql - Sorting night shift employees tasks based on start_time

PreethiN
June 8, 2024
257 views
1 vote
2 Answers

//night shift employee
id  from_time    to_time   task
1    21:00:00    22:00:00 - Cleaning(some task)
1    22:00:00    23:30:00 - Fumigation(can be some other task also)
1    4:00:00     7:00:00 - Disinfection
1    2:00:00     4:00:00 - Break
1    23:30:00    2:00:00 - Fogging

//day shift employee

2  09:00:00    10:00:00 - Cleaning(some task)
2  16:00:00    18:30:00 - Disinfection
2  11:30:00    14:00:00 - Fumigation(can be some other task also)
2  14:00:00    16:00:00 - Fogging
2  10:00:00    11:30:00 - Break

I need to sort the tasks based from_time in ascending order

SELECT * FROM testing WHERE emp_id='2' ORDER BY from_time ASC

The above query works fine for day shift employee but not sorting for night shift

SELECT * FROM testing WHERE emp_id='1'
ORDER BY 
  CASE 
    WHEN CAST(from_time AS TIME) > '12:00:00' THEN 1
    ELSE 2
  END,
  CAST(from_time AS TIME) ASC;

The above query works fine for night shift emplyee but not sorting for day shift

How can I solve this issue?

I need one query which can sort both night shift and day shift employees tasks based on from_time.

Answers

You could check the time difference between the minimum and maximum from_time per employee shift:

SELECT *
FROM testing t1
JOIN (
    SELECT id, TIMEDIFF(MAX(from_time), MIN(from_time)) AS diff
    FROM testing
    GROUP BY id
) t2 ON t1.id = t2.id
ORDER BY t1.id, IF(t2.diff > '12:00' AND from_time < '12:00', from_time + INTERVAL 24 HOUR, from_time);

Outputs:

id	from_time	to_time	task
1	21:00:00	22:00:00	Cleaning(some task)
1	22:00:00	23:30:00	Fumigation
1	23:30:00	02:00:00	Fogging
1	02:00:00	04:00:00	Break
1	04:00:00	07:00:00	Disinfection
2	09:00:00	10:00:00	Cleaning(some task)
2	10:00:00	11:30:00	Break
2	11:30:00	14:00:00	Fumigation(can be some other task also)
2	14:00:00	16:00:00	Fogging
2	16:00:00	18:30:00	Disinfection

Here’s a db<>fiddle.

It would be better to link to the start of the shift in some way, but this may work depending on your shift patterns.

Add date to your FROM_TIME and correct it after midnight as next day. Your Order By could have this code:

         ADDTIME(DATE_ADD(CURRENT_DATE, 
                          INTERVAL 
                          Case When CONVERT(SubStr(FROM_TIME, 1, INSTR(FROM_TIME, ':') - 1), DECIMAL) < 12 And 
                                    SHIFT_ID = 1
                               Then 1 
                          Else 0 
                          End DAY), 
                 Cast(FROM_TIME as TIME))

This adds from_time to current date and adds 1 day for ID = 1 if from time hour is less than 12. If your night shift is from 19:00 today untill 07:00 tommorrow then put < 7 instead of < 12 in case expression’s condition. Order by this is order by DATETIME.
Sample data, SQL code and result are below:

WITH    --  S a m p l e    D a t a :
    shift_tasks (SHIFT_ID, FROM_TIME, TO_TIME, TASK) AS
        ( Select   1, '21:00:00', '22:00:00', 'Cleaning'     Union All
          Select   1, '22:00:00', '23:30:00', 'Fumigation'   Union All
          Select   1, '4:00:00',  '7:00:00',  'Disinfection' Union All
          Select   1, '2:00:00',  '4:00:00',  'Break'        Union All
          Select   1, '23:30:00', '2:00:00',  'Fogging'      Union All
          --
          Select   2, '09:00:00', '10:00:00', 'Cleaning'     Union All
          Select   2, '16:00:00', '18:30:00', 'Disinfection' Union All
          Select   2, '11:30:00', '14:00:00', 'Fumigation'   Union All
          Select   2, '14:00:00', '16:00:00', 'Fogging'      Union All
          Select   2, '10:00:00', '11:30:00', 'Break' 
        )

--    S Q L :
Select   SHIFT_ID, FROM_TIME, TO_TIME, TASK
From     shift_tasks
Order By SHIFT_ID, 
         ADDTIME(DATE_ADD(CURRENT_DATE, 
                          INTERVAL 
                          Case When CONVERT(SubStr(FROM_TIME, 1, INSTR(FROM_TIME, ':') - 1), DECIMAL) < 12 And 
                                    SHIFT_ID = 1
                               Then 1 
                          Else 0 
                          End DAY), 
                 Cast(FROM_TIME as TIME))

/*      R e s u l t :
SHIFT_ID    FROM_TIME   TO_TIME    TASK
--------    ---------   ---------  ----------------------
       1    21:00:00    22:00:00   Cleaning
       1    22:00:00    23:30:00   Fumigation
       1    23:30:00    2:00:00    Fogging
       1    2:00:00     4:00:00    Break
       1    4:00:00     7:00:00    Disinfection
       2    09:00:00    10:00:00   Cleaning
       2    10:00:00    11:30:00   Break
       2    11:30:00    14:00:00   Fumigation
       2    14:00:00    16:00:00   Fogging
       2    16:00:00    18:30:00   Disinfection          */

Without SHIFT_ID in Order By clause the result should be …

/*      R e s u l t :
SHIFT_ID    FROM_TIME   TO_TIME    TASK
--------    ---------   ---------  ----------------------
       2    09:00:00    10:00:00   Cleaning
       2    10:00:00    11:30:00   Break
       2    11:30:00    14:00:00   Fumigation
       2    14:00:00    16:00:00   Fogging
       2    16:00:00    18:30:00   Disinfection          
       1    21:00:00    22:00:00   Cleaning
       1    22:00:00    23:30:00   Fumigation
       1    23:30:00    2:00:00    Fogging
       1    2:00:00     4:00:00    Break
       1    4:00:00     7:00:00    Disinfection            */

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Sorting night shift employees tasks based on start_time

Answers