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

SQL - Calculate hours worked during lunch break on nightshift

Hi,

I have a query that calculates the hours worked by an employee during a night shift. I need to calculate if they worked during the lunch break(ie, if they clocked out, then they took a lunch break, if not, then they worked during the lunch break).  There are fields for BreakStart and BreakEnd so the user stipulate what the break times are.  See my code below.

Can anyone help?
; 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, 
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

0
NerishaB
Asked:
NerishaB
1 Solution
 
8080_DiverCommented:
There are fields for BreakStart and BreakEnd so the user stipulate what the break times are.

When you refer to the user, are you meaning the person for whom the hours worked are being calculated or the person who, in effect, is requesting the calculations be performed.  In other words, is there someone setting a "normal lunch break time"?

Essentially, I can think of 2 approaches (basing it on each person for a shift):
1) Basing the decision on total time worked:
     a) Calculate the total time between the earliest [b]ClockIn[/b] time and 
          the latest [b]ClockOut[/b] time;
     b) Calculate the sum of the individual [b]ClockOut[/b] - [b]ClockIn[/b] 
          calculations;
     c)  If the calculation in b) is the same as the calculation in a), then they worked through lunch.

2) Basing the decision on the defined lunch break times:
     a) If a [b]ClockIn[/b]/[b]ClockOut[/b] entry spans the Lunch-Break times, 
          then they worked through lunch;
     b) If a [b]ClockIn[/b] is earlier than (i.e. less than) the start of the 
          Lunch Break and the [b]clockOut[/b] time is between the start 
          and end of the Lunch Break, then the person worked a portion 
          of the lunch break;
     c) If a [b]ClockIn[/b] is later than (i.e. greater than) the start of the 
          Lunch Break and the [b]clockOut[/b] time is between the start 
          and end of the Lunch Break, then the person worked a portion 
          of the lunch break;
     d) If a [b]ClockIn[/b] is later than (i.e. greater than) the start of the 
          Lunch Break and the [b]clockOut[/b] greater than end of the 
          Lunch Break, then the person worked a portion of the lunch break;

Open in new window


However, I am afraid that I don't have time to figure out your SQL and then try to create the appropriate subquery/subqueries that would need to be added.  It should be a fairly easy task for you to create the necessary calculations (especially since you are much more familier with the data than I am ;-).



0
 
NerishaBAuthor Commented:
thanks, I used your idea to get my solution.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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