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`