How to Group time segments and check break time

Question

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.

Answer 1

i believe if you want to combine both times you need to take them out of the group by and add sum them. based on the results the reporting can check total hours and break hours. you can add case statements if you want to flag them.

SELECT  ftc.lEmployeeID
       ,ftc.sFirstName
       ,ftc.sLastName
       ,SUM(ftc.TotalHours) AS TotalHours
       ,DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) AS BreakTimeMinutes
FROM dbo.fTimeCard(@StartDate, @EndDate,
                   @DeptList, @iActive,@ EmployeeList) AS ftc
WHERE SUM(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

I made this quick test in sql and it appears to work the way you want. did you add something to the group by?

declare @table table (emp_id int,name varchar(4), tin time,tout time);

insert into @table
VALUES (1,'d','8:30:00','11:35:00'),
    (1,'d','13:00:00','17:00:00');


SELECT t.emp_id
      ,t.name
      ,SUM(DATEDIFF(mi, tin,tout))/60 as hours
      ,DATEDIFF(mi, MIN(tout), MAX(tin)) AS BreakTimeMinutes
FROM @table t

GROUP BY t.emp_id, t.name