SQL Function that calculates Shift from StartTime and EndTime
- by Gentis
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`
