I am using SQL Server 2008. I have two table Shift and Break with following data: The shifts will be 7 days week.
Data in SHIFT TABLE
ID Desc Start_Time End_Time
1 1st 07:20:00 15:20:00
2 2nd 15:20:00 23:20:00
3 3rd 23:20:00 07:20:00
Data in BREAK TABLE
ID Desc Start_Time End_Time
1 1st Shift - 1st break 09:10:00 09:25:00
2 1st Shift - Lunch 11:30:00 12:05:00
3 1st Shift - 2nd break 13:30:00 13:45:00
4 2nd Shift - 1st break 17:10:00 17:25:00
5 2nd Shift - Lunch 19:30:00 20:05:00
6 2nd Shift - 2nd break 21:30:00 21:45:00
7 3rd Shift - 1st break 01:10:00 01:25:00
8 3rd Shift - Lunch 03:30:00 04:05:00
9 3rd Shift - 2nd break 05:30:00 05:45:00
The output needs to be something like:
Start_Time End Time
07:20:00 09:10:00
09:25:00 11:30:00
12:05:00 13:30:00
13:30:00 15:20:00
....
I am new with joins, Can anyone help me on join. Also if you think any changes in table structure will be helpful please suggest.