I am attempting to find all employees that worked during a 24 hour period. Their shift starts at 08:00 and ends the next day at 08:00. Most start at 08:00 and end the next day at 08:00. However, the start and end times may occur at any time during the 24 hour period. My goal would be to have all of those that worked during the 24 hour period to be marked as starting on the day the shift started.
For example, in the table below even though there are multiple start times over 2 days they all occurred within the 24 hours of the shift start time beginning on 7/26/22 at 08:00. I would like a new column showing a shift day of 7/26/22.
| first | start_time | end_time | hours |
|---|---|---|---|
| Nicole | 7/26/22 8:00 | 7/27/22 8:00 | 24 |
| Callan | 7/26/22 8:00 | 7/27/22 8:00 | 24 |
| Bob | 7/26/22 18:30 | 7/27/22 6:30 | 12 |
| Kevin | 7/27/22 7:00 | 7/27/22 8:00 | 1 |
| Michael | 7/27/22 7:00 | 7/27/22 8:00 | 1 |
WHERE Start_time <= @YourDateTime AND End_time >= @YourDateTime? - Larnu