Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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.
|GeoClustering and AOG||25||43|
|SQL Server 2016 (Win10) Developers all running built-in service accounts for SQL Server service on workstations: security concern||13||32|
|SQL Recursion schedule||13||34|
|find SQL job run average duration||24||55|