• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1439
  • Last Modified:

SQL calculate hours worked over shifts


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 summary

Open in new window

  • 3
  • 2
1 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.
NerishaBAuthor Commented:
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.
I got  suggestion.
Add column shift_offset_time. to your table.
set shift offset time to difference our regular day time - shift time
for exa:
our regular day time for the date May 10 2011 is 05/10/2011 00:00:000 to 05/10/2011 23:59:000
now your 1st shift starts at 05/10/2011 03:00:00.

The difference is 3 hours.

Now we have to do the below
1) set shift_offset time to 3
2) create stored proc. to convert the offset time to correct time
when you fetch the records. add the offset time to the checkin /checkout time retrieved.
and if the checkin/ checkout is 23:59:000, you need to change the date as well.

NerishaBAuthor Commented:
Can you help me with this, I am new to SQL
NerishaBAuthor Commented:
Thanks, I used your ideas to get a solution.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now