I have a stored function that pulls all employee clock in information. I'm trying to pull an exception report to audit lunches. My current query builds all info 1 segment at a time.
SELECT ftc.lEmployeeID, ftc.sFirstName, ftc.sLastName, ftc.dtTimeIn,
ftc.dtTimeOut, ftc.TotalHours, ftc.PunchedIn, ftc.Edited
FROM dbo.fTimeCard(@StartDate, @EndDate, @DeptList,
@iActive, @EmployeeList) AS ftc
LEFT OUTER JOIN Employees AS e ON ftc.lEmployeeID = e.lEmployeeID
WHERE (ftc.TotalHours >= 0) AND (ftc.DID IS NOT NULL) OR
(ftc.DID IS NOT NULL) AND (ftc.dtTimeOut IS NULL)
The output for this looks like this:
24 Bob bibby 8/2/2013 11:55:23 AM 8/2/2013 3:36:44 PM 3.68
24 bob bibby 8/2/2013 4:10:46 PM 8/2/2013 8:14:30 PM 4.07
39 rob blah 8/2/2013 8:01:57 AM 8/2/2013 5:01:40 PM 9.01
41 john doe 8/2/2013 10:09:58 AM 8/2/2013 1:33:38 PM 3.4
41 john doe 8/2/2013 1:55:56 PM 8/2/2013 6:10:15 PM 4.25
I need the query to do 2 things.
1) group the segments together for each day. 2) report the "break time" in a new colum
After I have that info I need to check the hours of each segment and make sure 2 things happen.
1) if they worked over a total of 6 hours, did they get a 30 minute break? 2) if they took a break, did they take a break > 30 minutes.
You see that Bob punched in at 11:55 AM and Punched out for lunch at 3:36. He punched back in from lunch at 4:10 and punched out at 8:14. He worked a total of 7.75 hours, and took over a 34 minute break. He was OK here. and I don't want to report an exception
John worked a total of 7.65 hours. However, when he punched out, he only took 22 minute lunch. I need to report "Jim only took 22 minute lunch"
You will also see rob worked 9 hours, without a break. I need to report "rob Worked over 6 hours and did not take a break"
I think if I can accomplish grouping the 2 segments. Then I can handle the reporting aspect.
*UPDATE**
I changed the query to try to accomplish this. Below is my current query:
SELECT ftc.lEmployeeID, ftc.sFirstName, ftc.sLastName, ftc.TotalHours, DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) AS Break_Time_Minutes
FROM dbo.fTimeCard(@StartDate, @EndDate, @DeptList, @iActive, @EmployeeList) AS ftc LEFT OUTER JOIN
Employees AS e ON ftc.lEmployeeID = e.lEmployeeID
WHERE (ftc.TotalHours >= 0) AND (ftc.DID IS NOT NULL) OR
(ftc.DID IS NOT NULL) AND (ftc.dtTimeOut IS NULL)
GROUP BY ftc.lEmployeeID, ftc.sFirstName, ftc.sLastName, ftc.TotalHours
My Output currently looks like this:
24 Bob bibby 3.68 -221
24 bob bibby 4.07 -244
39 rob blah 0.05 -3
39 rob blah 2.63 -158
41 john doe 3.4 -204
41 john doe 4.25 -255
As you can see It's not combining the segments by date and the Break_time is displaying negative minutes. It's also not combining the days. Bob's time should be on 1 line. and display 7.75 minutes break-time should 34 minutes.