EE helped me with some SQL code to create a report that shows all employees who worked more than 9 hours for a period of time. It works great (thank you EE) except that I didn't include an exception to the rule - which actually I'm not seeing why it's an exception. It looks like the code should handle it. But if an employee gets paid for a holiday (TimeSheetMin) and they also work the holiday it's coming up short on their time. The total should be 15 hours and 49 minutes but it only show 13 hours and 49 minutes.
Right now the code is doing all this...
No one gets paid for lunch (which is break flag 2 and 3) no matter how long they took so it's not included on the report.
If the break flag is 1 and under 15 minutes all is included.
If break flag is 1 and over 15 minutes - but under 25 then include 15 minutes but nothing more.
If the break is over 25 minutes the program "assumes" they meant to clock out to lunch and even tho the record will show a break code of 1, none of that is included in the total.
Here is my code... and I'm including an excel spreadsheet with the records causing the issue. Can you show me what I'm missing.
ALTER PROCEDURE [dbo].[spOver9Hours]
-- Add the parameters for the stored procedure here
@Supervisor as int,
@Employee as int
--get a unique instance number per employee per work day
; with EmployeeTimeInstances as
select EmployeeId as empId, TimeIn, TimeOut, TimeSheetMinutes, BreakFlag as Flag
, CAST(timein as DATE) as workDate
, ROW_NUMBER() over (Partition By EmployeeId, CAST(timein as DATE) Order By timein asc) as Instance_Counter
WHERE (CONVERT(datetime, CONVERT(char(8), TimeIn, 112)) BETWEEN @From AND @To)AND (Company = 1)
--get work time in, work time out, break time in, break time out
, EmployeeTimeDetail as
a.empId as empId
, a.workDate as workDate
, a.timein as timeIn
, a.timeout as timeOut
, a.timeout as breakIn
, b.timein as breakOut
, a.flag as breakFlag
, a.TimeSheetMinutes as TimeSheetMin
from EmployeeTimeInstances a
left outer join EmployeeTimeInstances b on
a.empId = b.empId
and a.workDate = b.workDate
and a.Instance_Counter = (b.Instance_Counter - 1)
--calculate seconds between work units and break units
, Employee_Work_Units as
, DATEDIFF(ss, timein, timeout) as work_unit_time
--this is a break over 25 minutes, don't count it!
when breakFlag = 1 and DATEDIFF(ss, breakin, breakOut) > 1500 then 0
--this is a break between 15 and 25 minutes, we'll count 15 of it!
when breakFlag = 1 AND
DATEDIFF(ss, breakin, breakOut) between 900 and 1500 then 900
--this is a normal break (15 or less) just counting the actual time.
when breakFlag = 1 AND
DATEDIFF(ss, breakin, breakOut) < 900 then DATEDIFF(ss, breakin, breakOut)
-- don't pay for lunch time ever (break flag isn't 1)
end as break_unit_time
, ISNULL(TimeSheetMin, 0) * 60 as time_sheet_min
, Employee_Work_Units_Summary as
select empId, workdate, SUM(work_unit_time + break_unit_time + time_sheet_min) as Total_WorkDate_Units
group by empId, workdate
--return days/employees who worked more than 9 hours
select workdate, empId, L.LastName + ', ' + L.FirstName AS Name, ManagerUID,
convert(varchar(5), dateadd(second, Total_WorkDate_Units, '0:00:00'),108) as HrsMin
from Employee_Work_Units_Summary s inner join dbo.EmployeeList L on s.empId = L.EmployeeId
where Total_WorkDate_Units > 32400 AND (L.Suspend = 0) and (ManagerUID = ISNULL(NULLIF (@Supervisor, 0), ManagerUID)) AND (@Employee =0 or L.EmployeeId =@Employee)
Order by workdate, Name