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.

CodePudding user response：

If the start time is greater than or equal to 8 am you want the shift date to be the same date as the start time, otherwise it is the previous day:

create table #shifts
(
   [first] varchar(32), -- this is a very weird column name. first_name, perhaps?
   start_time datetime, 
   end_time datetime, 
   [hours] tinyint
);

set dateformat mdy; -- your sample data uses an ambiguous date format so I have to do this
insert #shifts values
('Nicole ', '7/26/22 08:00', '7/27/22 8:00', 24),
('Callan ', '7/26/22 08:00', '7/27/22 8:00', 24),
('Bob    ', '7/26/22 18:30', '7/27/22 6:30', 12),
('Kevin  ', '7/27/22 07:00', '7/27/22 8:00', 1),
('Michael', '7/27/22 07:00', '7/27/22 8:00', 1);

select   [first], 
         start_time, 
         end_time, 
         [hours],
         shift_date =   iif
                        (
                           cast(start_time as time) >= '8:00', 
                           cast(start_time as date),
                           cast(dateadd(day, -1, start_time) as date)
                        )
from     #shifts;

Produces: