SQLTeam.com | Weblogs | Forums

Split single row into multiple rows based on 2 Date and time constraint in SQL Server

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!