SQL calculate hours worked over shifts

Posted on 2011-05-09
Last Modified: 2012-05-11

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

Question by:NerishaB
    LVL 3

    Expert Comment


    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.

    Author Comment

    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.
    LVL 3

    Accepted Solution

    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.


    Author Comment

    Can you help me with this, I am new to SQL

    Author Closing Comment

    Thanks, I used your ideas to get a solution.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now