Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers. It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.
ALTER PROCEDURE [dbo].[spOver9Hours] -- Add the parameters for the stored procedure here ( @From datetime, @To datetime, @Supervisor as int, @Employee as int ) AS --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 from dbo.EmployeeHours 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 ( select 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 ( select empId , workdate , DATEDIFF(ss, timein, timeout) as work_unit_time , case --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) else 0 end as break_unit_time , ISNULL(TimeSheetMin, 0) * 60 as time_sheet_min from EmployeeTimeDetail ) --summarize workunits , Employee_Work_Units_Summary as ( select empId, workdate, SUM(work_unit_time + break_unit_time + time_sheet_min) as Total_WorkDate_Units from Employee_Work_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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.