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

Select allOpen in new window