Solved

# SQL - Calculate hours worked during lunch break on nightshift

Posted on 2011-05-09
1,259 Views
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
``````
0
Question by:NerishaB

LVL 22

Accepted Solution

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;
``````

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

Author Closing Comment

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

## Featured Post

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 …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…