SQL calculate hours worked over shifts
Hi,
I need to calculate hours worked during a nightshift. For eg, the shift starts at 18:00, and ends at 3:00 the next morning. The employee may also take a lunch break during this tiime, but if the person took a lunch break at 10:00PM and came back at 11:00PM, my query tells me that the person's last clock-out was 10:00PM. I need the last clock-out to be 3:00AM. Can anyone help? See my code below.
I need to calculate hours worked during a nightshift. For eg, the shift starts at 18:00, and ends at 3:00 the next morning. The employee may also take a lunch break during this tiime, but if the person took a lunch break at 10:00PM and came back at 11:00PM, my query tells me that the person's last clock-out was 10:00PM. I need the last clock-out to be 3:00AM. Can anyone help? See my code below.
; with emp_clock as (Select ShiftRulesGroup_ID = ShiftRules.ShiftRulesGroup_ID, Personel_ID = Pers.Personel_ID, SRDay_ID = SRDay.SRDay_ID, NormalDay = case when SRDay.NormalDay = 1 then 'True' when SRDay.NormalDay = 0 then 'False' end, FullName = Pers.[Name] + ' ' + Pers.Surname, DailyHrsName = LEFT(Pers.[Name], 3)+ convert(nvarchar(10),RC.ClockTime, 12),
RowNo = row_number() over (partition by Pers.Personel_ID order by RC.ClockTime),
ClockDate = dateadd(day, datediff(day, 0, RC.ClockTime), 0),
DayAdj = case when Default_Shift_End < Default_Shift_Start then 0 else 1 end,
ClockTime = RC.ClockTime,
DirectionName = Dir.[Name],
ShiftRuleGroup = ShiftRules.[Name],
ShiftStartTime = convert(varchar(10), ShiftRules.Default_Shift_Start, 108),
ShiftEndTime = convert(varchar(10), ShiftRules.Default_Shift_End, 108), StartRoundingUp = SRDay.StartRounding_Up, EndRoundingUp = SRDay.EndRounding_Up,
StartRoundingHours = SRDay.StartRounding_Hrs_Num,
EndRoundingHours = SRDay.EndRounding_Num_Hrs,
Rules_SRBreak = SRBreak.[Name],
BreakStartTime = SRBreak.SR_Break_StartTime,
BreakEndTime = SRBreak.SR_Break_EndTime
FROM Personnel Pers
INNER JOIN RawClocks RC ON Pers.Personel_ID = RC.Person_ID
INNER JOIN Direction Dir ON RC.Direction_ID = Dir.Direction_ID
INNER JOIN Emp_ShiftGroup EmpShiftGroup ON Pers.Personel_ID = EmpShiftGroup.Employee_ID
INNER JOIN Rules_ShiftRulesGroup ShiftRules ON ShiftRules.ShiftRulesGroup_ID = EmpShiftGroup.ShiftRulesGroup_ID
INNER JOIN Rules_SRDay SRDay ON ShiftRules.ShiftRulesGroup_ID = SRDay.ShiftRulesGroup_ID
AND (dateadd(day, datediff(day, 0, RC.ClockTime), 0) = dateadd(day, datediff(day, 0, SRDay.Start_Time), 0))
LEFT OUTER JOIN Rules_SRBreak SRBreak ON SRBreak.SRDay_ID = SRDay.SRDay_ID), timelog
as (select
ShiftRulesGroup_ID = coalesce(ci.ShiftRulesGroup_ID, co.ShiftRulesGroup_ID), Personel_ID = coalesce(ci.Personel_ID, co.Personel_ID), SRDay_ID = coalesce(ci.SRDay_ID, co.SRDay_ID), NormalDay = coalesce(ci.NormalDay, co.NormalDay), FullName = coalesce(ci.FullName, co.FullName), DailyHrsName = coalesce(ci.DailyHrsName, co.DailyHrsName), ClockTime = coalesce(ci.ClockTime, co.ClockTime), ShiftRuleGroup = coalesce(ci.ShiftRuleGroup, co.ShiftRuleGroup), ShiftStartTime = coalesce(ci.ShiftStartTime, co.ShiftStartTime), ShiftEndTime = coalesce(ci.ShiftEndTime, co.ShiftEndTime), StartRoundingUp = coalesce(ci.StartRoundingUp, co.StartRoundingUp), EndRoundingUp = coalesce(ci.EndRoundingUp, co.EndRoundingUp), StartRoundingHours = coalesce(ci.StartRoundingHours, co.StartRoundingHours), EndRoundingHours = coalesce(ci.EndRoundingHours, co.EndRoundingHours),
Rules_SRBreak = coalesce(ci.Rules_SRBreak, co.Rules_SRBreak),
BreakStartTime = coalesce(ci.BreakStartTime, co.BreakStartTime),
BreakEndTime = coalesce(ci.BreakEndTime, co.BreakEndTime),
ClockIn = coalesce(ci.ClockTime, co.ClockDate - co.DayAdj + co.ShiftStartTime),
ClockOut = coalesce(co.ClockTime, ci.ClockDate + ci.DayAdj + ci.ShiftEndTime)
from emp_clock ci full outer join emp_clock co on ci.Personel_ID = co.Personel_ID
and ci.RowNo = co.RowNo - 1 and co.ClockTime < dateadd(day, 1, ci.ClockDate) + ci.ShiftStartTime
where not (ci.DirectionName is null and co.DirectionName = 'IN') and not (ci.DirectionName = 'OUT' and co.DirectionName is null) and (ci.DirectionName = 'IN' or ci.DirectionName is null)), detail as ( select ShiftRulesGroup_ID, Personel_ID, SRDay_ID, NormalDay, FullName, DailyHrsName, ClockTime, ClockIn,ClockOut, ShiftStartTime, ShiftEndTime, StartRoundingUp, EndRoundingUp, StartRoundingHours, EndRoundingHours, BreakStartTime, BreakEndTime,
TimeBeforeShiftStart = case when ClockIn - dateadd(day, datediff(day, 0, ClockIn), 0) < ShiftStartTime then ClockIn - dateadd(day, datediff(day, 0, ClockIn), 0) end,
TimeBeforeShiftEnd = case when ClockIn - dateadd(day, datediff(day, 0, ClockIn), 0) < ShiftStartTime then ShiftStartTime end,
TimeAfterShiftStart = case when ClockOut - dateadd(day, datediff(day, 0, ClockOut), 0) > ShiftEndTime then ShiftEndTime end,
TimeAfterShiftEnd = case when ClockOut - dateadd(day, datediff(day, 0, ClockOut), 0) > ShiftEndTime then ClockOut - dateadd(day, datediff(day, 0, ClockOut), 0) end from timelog),
summary as ( select Personel_ID, ShiftrulesGroup_ID, SRDay_ID, NormalDay, FullName, DailyHrsName, ClockDate = convert(nvarchar(10), dateadd(day, datediff(day, 0, ClockIn), 0),111), MIN(ClockTime) AS Calc_First_Clock, MAX(ClockOut) as Calc_Last_Clock, Calc_work_Start_Time = case when StartRoundingUp = 1 then CAST(CAST(FLOOR(CAST(MIN(ClockIn) AS FLOAT)) + CEILING((CAST(MIN(ClockIN) AS FLOAT)-FLOOR(CAST(MIN(ClockIn) AS FLOAT)))*24.0/ StartRoundingHours)* StartRoundingHours/24.0 AS DATETIME) AS SMALLDATETIME) when StartRoundingUp = 0 then CAST(CAST(FLOOR(CAST(MIN(ClockIn) AS FLOAT)) + ROUND((CAST(MIN(ClockIn) AS FLOAT)- FLOOR(CAST(MIN(ClockIn) AS FLOAT)))*24.0/StartRoundingHours,0)*StartRoundingHours/24.0 AS DATETIME) AS SMALLDATETIME) end, Calc_work_end_time = case when EndRoundingUp = 1 then CAST(CAST(FLOOR(CAST(MAX(ClockOut) AS FLOAT)) + CEILING((CAST(MAX(ClockOut) AS FLOAT)-FLOOR(CAST(MAX(ClockOut) AS FLOAT)))*24.0/EndRoundingHours)* EndRoundingHours/24.0 AS DATETIME) AS SMALLDATETIME) when EndRoundingUp = 0 then CAST(CAST(FLOOR(CAST(MAX(ClockOut) AS FLOAT)) + ROUND((CAST(MAX(ClockOut) AS FLOAT)-FLOOR(CAST(MAX(ClockOut) AS FLOAT)))*24.0/EndRoundingHours,0)* EndRoundingHours/24.0 AS DATETIME) AS SMALLDATETIME) end,
TotalWorkHours = CAST(sum(datediff(minute, ClockIn, ClockOut) / 60.0) as decimal(4,2)),
Before_Shift = CAST(isnull(sum(datediff(minute, TimeBeforeShiftStart, TimeBeforeShiftEnd)), 0) / 60.0 as decimal(4,2)),
After_Shift = CAST(isnull(sum(datediff(minute, TimeAfterShiftStart, TimeAfterShiftEnd)), 0) / 60.0 as decimal(4,2)) from detail
group by SRDay_ID, dateadd(day, datediff(day, 0, ClockTime), 0), Personel_ID, FullName, ShiftRulesGroup_ID, NormalDay, DailyHrsName, ClockIn,ClockOut, ShiftStartTime, ShiftEndTime, StartRoundingUp, EndRoundingUp, StartRoundingHours, EndRoundingHours, BreakStartTime, BreakEndTime) select * from summaryASKER
Thanks, the problem that I am now having is that when the person takes their break at 22:00 and comes back at 23:00, it looks as if the person clocked out for the day, then clocked back in again on a different shift. In other words, their first clockin is 18:00, but their last clockout is 22:00.
This is not correct, the last clockout should be 3:00 the next morning.
This is not correct, the last clockout should be 3:00 the next morning.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you help me with this, I am new to SQL
ASKER
Thanks, I used your ideas to get a solution.
1) Add a column lastupdate to you timein and out table.
2) when the individual check in and check out. update the table -> shift, time in /out, last update.
3) when selecting data filter on shift and last update date.