Solved

SQL code for - time worked greater than 9 hours

Posted on 2013-01-23
7
496 Views
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
)	
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

Open in new window

Error-Example.xls
0
Comment
Question by:BobRosas
  • 3
  • 3
7 Comments
 
LVL 26

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?
0
 

Author Comment

by:BobRosas
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?
0
 
LVL 39

Assisted Solution

by:appari
appari earned 250 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.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 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.
0
 

Author Comment

by:BobRosas
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
0
 

Author Comment

by:BobRosas
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!
0
 
LVL 26

Expert Comment

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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now