Need elegant T-SQL solution
I am looking for a somewhat elegant solution to my problem if possible. Looking at the example table below, I need the records whose ShiftStartTime > ShiftEndTime to be split in two records to the time will account for a fill 24 hours.
Take StartDayOfWeek = 2 for example. I would like the result to return 4 records for that DoW...
Take StartDayOfWeek = 2 for example. I would like the result to return 4 records for that DoW...
StartDayOfWeek ShiftStartTime ShiftEndTime ShiftLengthInHours
2 00:00:00 06:00:00 6
2 06:00:00 14:00:00 8
2 14:00:00 22:00:00 8
2 22:00:00 24:00:00 2StartDayOfWeek ShiftStartTime ShiftEndTime ShiftLengthInHours
2 06:00:00 14:00:00 8.0000
2 14:00:00 22:00:00 8.0000
2 22:00:00 06:00:00 8.0000
3 06:00:00 14:00:00 8.0000
3 14:00:00 22:00:00 8.0000
3 22:00:00 06:00:00 8.0000
4 06:00:00 14:00:00 8.0000
4 14:00:00 22:00:00 8.0000
4 22:00:00 06:00:00 8.0000
5 06:00:00 14:00:00 8.0000
5 14:00:00 22:00:00 8.0000
5 22:00:00 06:00:00 8.0000
6 06:00:00 14:00:00 8.0000
6 14:00:00 22:00:00 8.0000
6 22:00:00 06:00:00 8.0000
1 22:00:00 06:00:00 8.0000
7 06:00:00 14:00:00 8.0000
7 14:00:00 22:00:00 8.0000ASKER
The input data is the second table above, and the desired output is the first.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well done, thank you all
Welcome. Good luck on future qs as well.
Can you provide the input data you have. and exact output you want.