SQL Function that calculates Shift from StartTime and EndTime

Question

Hello Folks I have been trying to get a function going that calculates what Shift the Employees worked from their StartTime and EndTime. Here is the code i have so far, there seems to be calculating the shift wrong.

Shift 1 from 08:00:00 - 16:30:00
Shift 2 from 16:00:00 - 00:30:00
Shift 3 from 00:00:00 - 08:30:00

Also the shift with most hours wins for times between shifts.

Thanks, G

---

`FUNCTION [dbo].[ShiftDifferential] (
 @StartTime time(0), 
 @EndTime time(0)
 )
RETURNS int
AS
BEGIN
 --DECLARE @StartTime time(0)
 --DECLARE @EndTime time(0)

 -- Declare the return variable here
 DECLARE @Shift1StartTime time(0)
 DECLARE @Shift2StartTime time(0)
 DECLARE @Shift3StartTime time(0)
 DECLARE @Shift1EndTime time(0)
 DECLARE @Shift2EndTime time(0)
 DECLARE @Shift3EndTime time(0)

 DECLARE @HrsShift1 decimal(18,2)
 DECLARE @HrsShift2 decimal(18,2)
 DECLARE @HrsShift3 decimal(18,2)

 DECLARE @ShiftDiff int

 --SET @StartTime = '09:00:00'
 --SET @EndTime = '13:00:00'

 SET @Shift1StartTime = '08:00:00' 
 SET @Shift2StartTime = '16:00:00'
 SET @Shift3StartTime = '00:00:00'
 SET @Shift1EndTime = '16:30:00'
 SET @Shift2EndTime = '00:30:00'
 SET @Shift3EndTime = '08:30:00'



 --SELECT DATEDIFF(HH, @Shift1StartTime, @EndTime)

 -- hours are between shift 3 and shift 1
 if DATEDIFF(HH, @Shift1StartTime, @StartTime) < 0 AND (DATEDIFF(hh, @Shift1StartTime, @EndTime) < 8.0 AND DATEDIFF(hh, @Shift1StartTime, @EndTime) > 0)
  begin
   --PRINT 'Shift 3-1 step1'
   SET @HrsShift3 = DATEDIFF(HH, @StartTime, @Shift1StartTime)
   SET @HrsShift1 = DATEDIFF(HH, @Shift1StartTime, @Endtime)
   --PRINT @HrsShift3
   --PRINT @HrsShift1

   -- get shift with most hours
   if @HrsShift3 > @HrsShift1
    begin
     SET @ShiftDiff = 3
    end
   else
    begin
     SET @ShiftDiff = 1
    end
  end

 -- hours are in shift 1 
 if (DATEDIFF(HH, @Shift1StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift1StartTime, @EndTime) <= 8) 
   OR (DATEDIFF(HH, @Shift1StartTime, @StartTime) > 0  AND DATEDIFF(HH, @Shift1StartTime, @EndTime) <= 8)
  begin
   --PRINT 'Shift 1 step2'
   SET @HrsShift3 = 0
   SET @HrsShift1 = DATEDIFF(HH, @StartTime, @EndTime)
   --PRINT @HrsShift3
   --PRINT @HrsShift1

   -- only one shift with hours
   SET @ShiftDiff = 1
  end

 -- hours are between shift 1 and shift 2
 if DATEDIFF(HH, @Shift2StartTime, @StartTime) < 0 and (DATEDIFF(HH, @Shift2StartTime, @EndTime) < 8.0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) > 0)  
  begin
   --PRINT 'Shift 1-2 step1' 
   SET @HrsShift1 = DATEDIFF(HH, @StartTime, @Shift2StartTime)
   SET @HrsShift2 = DATEDIFF(HH, @Shift2StartTime, @Endtime)
   --PRINT @HrsShift1
   --PRINT @HrsShift2

   -- get the shift with most hours
   if @HrsShift1 > @HrsShift2
    begin
     SET @ShiftDiff = 1
    end
   else
    begin
     SET @ShiftDiff = 2
    end
  end

 -- hours are in shift 2 
 if (DATEDIFF(HH, @Shift2StartTime, @StartTime) = 0 AND DATEDIFF(HH, @Shift2StartTime, @EndTime) <= 8) 
  OR (DATEDIFF(HH, @Shift2StartTime, @StartTime) > 0  AND DATEDIFF(HH, @Shift2StartTime, @EndTime) <= 8)
  begin
   --PRINT 'Shift 2 step2'
   SET @HrsShift3 = 0
   SET @HrsShift1 = DATEDIFF(HH, @StartTime, @EndTime)
   --PRINT @HrsShift3
   --PRINT @HrsShift1   

   -- only one shift with hours
   SET @ShiftDiff = 2
  end

 -- hours are between shift 2 and shift 3 - overnight shift
 if DATEDIFF(HH, @StartTime, @EndTime) < 0  
  begin
   --PRINT 'Shift 2-3 step1' 
   SET @HrsShift2 = DATEDIFF(HH, @StartTime, '23:59:59') + DATEDIFF(HH, '00:00:00', '00:30:00')
   SET @HrsShift3 = DATEDIFF(HH, '00:30:00', @EndTime)
   --PRINT @HrsShift2
   --PRINT @HrsShift3

   -- get the shift with most hours
   if @HrsShift2 > @HrsShift3
    begin
     SET @ShiftDiff = 2
    end
   else
    begin
     SET @ShiftDiff = 3
    end
  end

 -- hours are in shift 3 
 if (DATEDIFF(HH, @Shift3StartTime, @StartTime) = 0  AND DATEDIFF(HH, @Shift3StartTime, @EndTime) <= 8)
  OR (DATEDIFF(HH, @Shift3StartTime, @StartTime) > 0  AND DATEDIFF(HH, @Shift3StartTime, @EndTime) <= 8)
  begin
   --PRINT 'Shift 3 step2'
   SET @HrsShift2 = 0
   SET @HrsShift3 = DATEDIFF(HH, @StartTime, @EndTime)
   --PRINT @HrsShift2
   --PRINT @HrsShift3   

   -- only one shift with hours
   SET @ShiftDiff = 3
  end

 RETURN @ShiftDiff;
END`

Answer 1

It feels like you're trying to take an iterative approach to a set-based problem.

It sounds to me like you're trying to find the shift which overlaps the most with the times entered on a Timesheet entry.

Work through this, and you'll see it's very set-based, and easily expandable because you can use a table of Times instead if you prefer. This assumes that things start off using the time type, but I cast it to datetime because the shift might end the following day.

with 
Shifts as
(
SELECT ShiftID, cast(StartTime as datetime) as StartTime, 
    case 
      when EndTime < StartTime 
      then dateadd(day,1,cast(EndTime as datetime)) 
      else cast(EndTime as datetime) 
    end as EndTime
FROM Shifts
),
Times as 
(select 1 as TimeID,
    cast(@StartTime as datetime) as StartTime, 
    case 
      when @EndTime < @StartTime 
      then dateadd(day,1,cast(@EndTime as datetime)) 
      else cast(@EndTime as datetime) 
    end as EndTime
),
Overlaps as
(
select s.ShiftID, t.TimeID, 
       case when s.StartTime > t.StartTime then s.StartTime else t.StartTime end as StartOverlap,
       case when s.EndTime < t.EndTime then s.EndTime else t.EndTime end as EndOverlap,
from Shifts s
cross join Times t
),
OrderedOverlaps as
(
select *, row_number() over(partition by TimeID order by datediff(min,StartOverlap,EndOverlap) desc) as RowNum
from Overlaps
)
select s.ShiftID, t.TimeID
from OrderedOverlaps
where RowNum = 1;