Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL code for - time worked greater than 9 hours

Posted on 2013-01-23
Medium Priority
Last Modified: 2013-01-24
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
	@From datetime,
	@To datetime,
	@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
	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
		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
	,	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

Open in new window

Question by:BobRosas
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38812598
I run your query against your sample data and it gives me 15 hours and 30 minutes not the 13- 49 you say it gives you or the 15-49 you say you expect.  Am I missing something?

Author Comment

ID: 38812629
No...I'm obviously missing something.  I'm runing the code right from SQL Server and it's giving me 13:30 instead of 15:30.  (49 was a different record - Sorry!).  I went back and ran it again...verifying it's the correct record and I'm still getting 13:30.  I'm not seeing what I'm doing wrong.  

I copied the data from the table to Excel and had to change the date format and that's the only difference I can find.  The table uses AM/PM so instead of 13:30 the table shows 1:30 PM and instead of 15:00 it shows 3:00 PM.   That's not it right?
LVL 39

Assisted Solution

appari earned 1000 total points
ID: 38812687
with the data you posted i am also getting 15:30.
Do you have multiple databases? in that case check if the DB you are copying the data from and the procedure you are running are on same DB.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 27

Accepted Solution

Chris Luttrell earned 1000 total points
ID: 38813213
Either 13:30 or 1:30 PM work, they are just display format differences, you can change the display in either SQL or Excel at will.  That will not be it.
The first time I coppied your example SP above I got a USE [TimeClockPlus] statement at the top, could it be like appari asked and you are looking at a different table/database, I do not see anything else that explains it yet.  Still looking and testing.
Maybe dump the raw data here as text in case there is something getting converted with Excel.

Author Comment

ID: 38815081
Thank you both for your help.  I verified the db is correct and I tried re-running everything again this morning.  I know it's got to be something simple but I'm not seeing it.  I'm still getting 13:30.  Here is a dump of the raw data.  I hope this tells  you something because I don't know where else to look.

RecordId      GUID      EmployeeId      Company      TimeIn      TimeOut      JobCode      CostCode      Rate      Tracked1      Tracked2      Tracked3      BreakFlag      ManagerApproval      EmployeeApproval      OtherApproval      InVariance      OutVariance      Flag      ShiftNumber      SDProcessed      SDPremium      InLocationID      OutLocationID      MissedInPunch      MissedInApproval      MissedOutPunch      MissedOutApproval      TimeSheetMinutes      UTCDateAdded      UTCDateModified      MergeInId      MergeOutId      TZOffset      TeacherId      TimeStampCol      TimeOffset      EmployeeApprovalUID      ManagerApprovalUID      OtherApprovalUID
2276484      {39B0C0FE-FFCE-405E-BE0F-B271C9739D4C}      907      1      1/1/2013 7:00:00 AM      1/1/2013 7:00:00 AM      3            $0.00                        0      -1      0      0      0      0      66051      0      0      $0.00      237            0      0      0      0      480      1/2/2013 6:16:00 PM      1/15/2013 12:19:00 AM                                    0            71      
2287739      {488FE3A6-3908-4A01-B74E-FA23CF9A0FD9}      907      1      1/1/2013 7:00:00 AM      1/1/2013 8:45:00 AM      1093            $0.00                        1      -1      0      0      0      0      66051      0      0      $0.00      237      237      0      0      0      0            1/15/2013 12:18:00 AM      1/15/2013 12:19:00 AM                                    0            71      
2287740      {DA7ED041-6B19-4357-9397-53F4C8EC343C}      907      1      1/1/2013 9:00:00 AM      1/1/2013 11:00:00 AM      1093            $0.00                        2      -1      0      0      0      0      66051      0      0      $0.00      237      237      0      0      0      0            1/15/2013 12:19:00 AM      1/15/2013 12:19:00 AM                                    0            71      
2287741      {704FF955-A64E-489C-8108-6A51388A3177}      907      1      1/1/2013 11:30:00 AM      1/1/2013 1:30:00 PM      1093            $0.00                        1      -1      0      0      0      0      66051      0      0      $0.00      237      237      0      0      0      0            1/15/2013 12:19:00 AM      1/15/2013 12:19:00 AM                                    0            71      
2287742      {87F7F4CB-860E-4893-B35C-58B3117FB848}      907      1      1/1/2013 1:45:00 PM      1/1/2013 3:00:00 PM      1093            $0.00                        0      -1      0      0      0      0      66051      0      0      $0.00      237      237      0      0      0      0            1/15/2013 12:19:00 AM      1/15/2013 12:19:00 AM                                    0            71

Author Comment

ID: 38815302
I think I found it...
When I ran the first part of the query all by itself...
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 '01/01/13' AND '01/01/13')AND (Company = 1)

Open in new window

The results put the records in a different order...
907      2013-01-01 07:00:00      2013-01-01 08:45:00      NULL      1      2013-01-01      1
907      2013-01-01 07:00:00      2013-01-01 07:00:00      480      0      2013-01-01      2
907      2013-01-01 09:00:00      2013-01-01 11:00:00      NULL      2      2013-01-01      3
907      2013-01-01 11:30:00      2013-01-01 13:30:00      NULL      1      2013-01-01      4
907      2013-01-01 13:45:00      2013-01-01 15:00:00      NULL      0      2013-01-01      5

The above order is not what the db shows when I filtered on the employee and sent you the records which is why it works for you.  So I changed the above code and added RecordId, TimeIn as the Order by  and finally got 15:30.  

Since the 2 dates match I'm surprised the db didn't give varied results.  I don't know why it consistently had an order different than the db order.  Thanks for sending me in the right direction with "raw data".    You two were a great help!
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38815318
Cool, glad something finally made sense and you could figure it out.

Featured Post

Fill in the form and get your FREE NFR key NOW!

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question