I need to get the difference between start times of different days. I am only looking for specific days where the second clock day is before the prevous clock in day. The best way to explain my issue would be with some examples.
clock in day 1 clock in day 2 time difference
2015-04-14 08:00:42.067 2015-04-15 07:59:59.717 .01 minute - valid
2015-04-12 10:52:58.463 2015-04-13 07:49:54.173 3.03 hours - valid
2015-04-13 08:00:23.065 2015-04-14 08:25:02.007 .25 minutes - not valid because the second time is later than the first time
The sql I am working with is close. My output is below but I need to save this time in the database as a float so I can then add it to other values in part of calculating an employees payroll for the week.
clock in day 1 clock in day 2 hh:mm
2015-04-15 07:56:17.913 2015-04-16 07:54:43.170 00:01:00.0000000
2015-04-16 07:54:43.170 2015-04-17 07:51:03.477 00:03:00.0000000
2015-04-17 07:53:46.260 2015-04-18 07:52:51.543 00:00:00.0000000 not valid and need to be excluded in where clause
So with the data in hh:mm columns, I would need to add that value to .15 or 40.00 (as examples) from the database which are stored as floats. My other issue would be the where clause to exclude anything less than 1 minute.
WITH CTE AS
ROW_NUMBER() OVER (PARTITION BY EMPLOYEEID ORDER BY MIN(CLOCK_IN_DATE_TIME)) RN
FROM Payroll.PUERTORICOTIMECARDS PR
WHERE PR.BUSINESS_DATE >= '04/12/2015'
AND PR.BUSINESS_DATE <= '04/18/2015'
GROUP BY PR.EMPLOYEEID,
cast (convert(varchar(5),(23-DateDiff(s, c1.clock_in, c2.clock_in)/3600))+':'+convert(varchar(5),(59-DateDiff(s, c1.clock_in, c2.clock_in)%3600/60)) as time) as [hh:mm]
FROM CTE C1
LEFT JOIN CTE C2
ON C1.EMPLOYEEID = C2.EMPLOYEEID
AND C1.RN + 1 = C2.RN
cast (convert(varchar(5),(23-DateDiff(s, c1.clock_in, c2.clock_in)/3600))+':'+convert(varchar(5),(59-DateDiff(s, c1.clock_in, c2.clock_in)%3600/60)) as time) > '00:00' (where clause does not work)