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.
axnst2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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
axnst2Author Commented:
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.
axnst2Author Commented:
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

Kyle AbrahamsSenior .Net DeveloperCommented:
if all you're looking to do is exclude the row where there's one punchout then you could add a having clause:

       GROUP BY PR.EMPLOYEEID, PR.BUSINESS_DATE
            HAVING SUM(PR.HOURS_WORKED) >= 8.00
                           and count(pr.business_date) > 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.