I want to split a single row into multiple rows based on time. Below are the example: First, I have table OT show for each Id_staff with working_date, start_time, end_time.
id id_staff working_date start_time end_time 1 1 2022-04-01 21:00:00 23:00:00 2 1 2022-04-02 04:00:00 08:00:00 3 1 2022-04-03 20:00:00 01:00:00
Then, I have table OT_shift show shifts of OT (Day-shift and Night-shift)
id shift_name start_time end_time 1 Day shift 06:00:00 22:00:00 2 Night shift 22:00:00 06:00:00
Expected output is below:
id id_staff working_date start_time end_time
1 1 2022-04-01 21:00:00 22:00:00
2 1 2022-04-01 22:00:00 23:00:00
3 1 2022-04-02 04:00:00 06:00:00
4 1 2022-04-02 06:00:00 08:00:00
5 1 2022-04-03 20:00:00 00:00:00
6 1 2022-04-03 00:00:00 01:00:00
I want to split data range from OT table to multiple records which start_time, end_time in the range of OT_shift (Dayshift or Nightshift)
There're some questions like this, but my problem is there're two date range in OT_shift table.
Please give me some solution. Thanks for any helps.
This is the first time I asked questions. If there are any mistakes, please forgive me.
Thanks so much!