asked on

Help with getting lunch time clock-in times in sql

I am working on calculating the 24 hour rule for Puerto Rico. The best way to explain this is through the following example.

            Day 1                                          Day 2
8:00 am -- 12:00 pm                        7:00 am -- 12:00 pm
12:00 pm -- 1:00 pm (meal)            12:00 pm -- 1:00 pm (meal)
1:00 pm -- 5:00 pm                        1:00 pm -- 5:00 pm

In Example I, notice that the employee commenced the shift one (1) hour earlier on the second day, in relation to the first day. Therefore, under the “24 hour” rule, the hour worked
between 7:00 am and 8:00 am on the second day is considered overtime. This conclusion is reached by back tracking the amount of hours worked between 8:00 am on the second day to 8:00
am on the first day. The sum of hours worked in this period is nine (9) hours, or one (1) in excess of the statutory eight (8) hour maximum. Thus, to determine if each hour worked on the
second day is in excess of eight (8) in the consecutive twenty four (24) hour period that concludes with said hour, you must count the hours worked in the preceding twenty four (24) hour
period.

Using the logic above, I have the following query that calculates this logic correctly except for one condition. If I have an employee that has not clocked out for the day, then when looking at the times for when an employee clocks back in after lunch, I am getting the employees next morning clock in. Below is the query and raw data.

      ;WITH CTE AS
      (
            SELECT       PR.EMPLOYEEID
                        , PR.BUSINESS_DATE
                        , MAX(PR.CLOCK_IN_DATE_TIME) CLOCK_IN
                        , SUM(PR.HOURS_WORKED) HRS_WORKED
                        ,ROW_NUMBER() OVER (PARTITION BY EMPLOYEEID ORDER BY MAX(CLOCK_IN_DATE_TIME)) RN
            FROM Payroll.PUERTORICOTIMECARDS PR
            WHERE PR.PAYROLLPOSTID = @PAYROLLPOSTID
            AND CONVERT(VARCHAR, CLOCK_OUT_DATE_TIME,113) NOT LIKE '%23:59:59:000' --employee did not clock out for the day
            GROUP BY PR.EMPLOYEEID, PR.BUSINESS_DATE
            HAVING SUM(PR.HOURS_WORKED) >= 8.00
      )

This is the example of what the above query returns. Noticed all the times are in the afternoon except the one on 5/14

EMPLOYEEID        BUSINESS_DATE        CLOCK_IN        HRS_WORKED        RN
868        2015-05-11 00:00:00.000      2015-05-11 14:01:35.897        8.266666        1
868        2015-05-12 00:00:00.000      2015-05-12 14:01:59.217        8.016666        2
868        2015-05-13 00:00:00.000      2015-05-13 14:01:12.927        8.299999        3
868        2015-05-14 00:00:00.000      2015-05-14 08:02:05.880        9.166666        4 bad row
868        2015-05-15 00:00:00.000      2015-05-15 14:01:41.430        8.116666        5

Here is the raw time card data.

employeeid        BUSINESS_DATE        CLOCK_IN_DATE_TIME      CLOCK_OUT_DATE_TIME      HOURS_WORKED
868        2015-05-11 00:00:00.000        2015-05-11 07:46:27.367        2015-05-11 13:03:00.230        5.283333
868        2015-05-11 00:00:00.000        2015-05-11 14:01:35.897        2015-05-11 17:00:13.823        2.983333
868        2015-05-12 00:00:00.000        2015-05-12 08:00:18.047        2015-05-12 13:01:53.227        5.016666
868        2015-05-12 00:00:00.000        2015-05-12 14:01:59.217        2015-05-12 17:01:25.530        3
868        2015-05-13 00:00:00.000        2015-05-13 08:00:22.910        2015-05-13 13:02:44.280        5.033333
868        2015-05-13 00:00:00.000        2015-05-13 14:01:12.927        2015-05-13 17:17:43.490        3.266666
868        2015-05-14 00:00:00.000        2015-05-14 08:02:05.880        2015-05-14 17:12:47.483        9.166666
868        2015-05-15 00:00:00.000        2015-05-15 07:57:26.483        2015-05-15 13:02:48.747        5.083333
868        2015-05-15 00:00:00.000        2015-05-15 14:01:41.430        2015-05-15 17:03:12.263        3.033333

How would I exclude a row (day) that employee did not clock out for lunch? I would also need to make sure I do not compare the 5/13 14:01 time to the 5/15 14:01 time.

Kyle Abrahams, PMP

That actually looks correct to me. What if you had an employee that worked through lunch?

I would also not exclude where the employee didn't clock out, but assume that they clocked out at the normal end of shift time.
eg:

case when CONVERT(VARCHAR, CLOCK_OUT_DATE_TIME,113) LIKE '%23:59:59:000' then
convert (varchar, CLOCK_OUT_DATE_TIME, 101) + '17:30:00' else
CLOCK_OUT_DATE_TIME
end

axnst2

ASKER

If an employee works through lunch, then he gets paid 1 double time. That is a different calculation on top of the 24 hour rule that I am working. As for excluding employees that did not clock out, I am inserting all this logic into a table for which I can run my aggregates against. If we find a day the employee did not clock out, we give them a standard 8 hours.

axnst2

ASKER

I just realized that I forgot to add one last piece of my logic. Here is the final query that does the calculation and finding the days that violate the rule.

SELECT  Employee_ID AS EMPLOYEEID
		, tt.Business_Date
		, tt.Hr_Rule
From (
		SELECT  Employee_ID AS EMPLOYEEID
		, tt.Business_Date
		, tt.Hr_Rule
From (
		Select C1.EMPLOYEEID
					, C2.BUSINESS_DATE
					, c1.HRS_WORKED
					, Convert(decimal(10,1),DATEPART(MINUTE, CAST(C2.CLOCK_IN AS DATETIME))) - Convert(decimal(10,1),DATEPART(MINUTE, CAST(C1.CLOCK_IN AS DATETIME))) As duration
					, CASE WHEN DATEDIFF(HOUR, C2.CLOCK_IN, DATEADD(DAY, 1,C1.CLOCK_IN)) >= 0 AND DATEDIFF(HOUR, C2.CLOCK_IN, DATEADD(DAY, 1,C1.CLOCK_IN)) <= 1 THEN (CONVERT(decimal(10,0), DATEPART(minute, CAST(C1.CLOCK_IN AS DATEtIME)))/100.00) + (Convert(decimal(10,1),60 - DATEPART(MINUTE, CAST(C2.CLOCK_IN AS DATETIME)))/100.00) 
					 WHEN DATEDIFF(HOUR, C2.CLOCK_IN, DATEADD(DAY, 1,C1.CLOCK_IN)) > 1 THEN DATEDIFF(hour, c2.clock_in, DATEADD(DAY, 1,C1.CLOCK_IN)) + ((CONVERT(decimal(10,0), DATEPART(minute, CAST(DATEADD(DAY, 1,C1.CLOCK_IN) AS DATEtIME)))/100.00) + (Convert(decimal(10,1),60 - DATEPART(MINUTE, CAST(C2.CLOCK_IN AS DATEtIME))))/100.00) end AS hr_rule
					 , C1.CLOCK_IN AS CLOCK_IN_1
					 , C2.CLOCK_IN AS CLOCK_IN_2
					FROM CTE C1
				LEFT JOIN CTE C2
					ON C1.EMPLOYEEID = C2.EMPLOYEEID
					AND C1.RN + 1 = C2.RN
					) As tt (Employee_ID, Business_Date, Hr_Work, Duration, Hr_Rule, CLOCK_1, CLOCK_2)
Where Duration > 0
and Hr_Work > 8.0
and DATEADD(day, 1, tt.CLOCK_1) > tt.CLOCK_2

Open in new window

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial