Databases
StackOverflow2013
Postsdbo
POHow to Group time segments and check break time
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to Group time segments and check break time
    primarykey
    data
    text
    <p>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.</p> <pre><code>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 &gt;= 0) AND (ftc.DID IS NOT NULL) OR (ftc.DID IS NOT NULL) AND (ftc.dtTimeOut IS NULL) </code></pre> <p>The output for this looks like this:</p> <pre><code>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 </code></pre> <p>I need the query to do 2 things.</p> <p>1) group the segments together for each day. 2) report the "break time" in a new colum</p> <p>After I have that info I need to check the hours of each segment and make sure 2 things happen.</p> <p>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.</p> <p>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</p> <p>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"</p> <p>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"</p> <p>I think if I can accomplish grouping the 2 segments. Then I can handle the reporting aspect.</p> <p><em><strong></em>*UPDATE**</strong></p> <p>I changed the query to try to accomplish this. Below is my current query:</p> <pre><code>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 &gt;= 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 </code></pre> <p>My Output currently looks like this:</p> <pre><code>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 </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAdminiculo
    1. USShmewnix
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to Group time segments and check break time
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to Group time segments and check break time
      1. USShmewnix
      1. VTBountyStart
    3. VO
      singulars
      1. POHow to Group time segments and check break time
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COfirst you use datediff(minute,outtime,intime) to get one more column. then again sum the datediff group by username. then use case statement for remarks.Show data in correct format aong with column name.
      singulars
      1. POHow to Group time segments and check break time
      1. USKumarHarsh
    2. COI have to combine the segments... I only want to report those who have taken a break less than 30 minutes, including those who didn't take one at all. I also need to report the total number of hours they worked without an adequate break
      singulars
      1. POHow to Group time segments and check break time
      1. USShmewnix
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload