sql sum duration within start and stop time stamps

Question

Is it possible to sum the duration based on the number of minutes one time stamp (start, stop) within an second?

This is what I would want to do.. return the number of minutes an employee was sick during their overtime shift. The records are in segments... a sick exception may not always be within an overtime shift.

All rows would be within the same table exceptions.

Employee Exception  Start   Stop
Bob      OverTime   09:00   17:00
Jim      OverTime   14:00   16:00
Mark     OverTime   14:00   18:00
Steve    OverTime   10:00   14:00

Employee  Exception  Start  Stop 
Bob      Sick        09:00  09:30
Bob      Sick        14:00  17:00
Jim      Sick        14:00  16:00

Results:

Bob: 3.5 hours
Jim: 2 hours

Would be nice if you sample include a case where sick wasnt inside overtime shift and desire result for that case, becasue right now is just a simple sum — Juan Carlos Oropeza, Nov 03 '15 at 20:14

JamieD77 · Answer 1 · 2015-11-03T20:32:43.943

3

SELECT 
    Employee,
    SUM(
        DATEDIFF(minute,
            (CASE WHEN SickStart > OvertimeStart THEN SickStart ELSE OvertimeStart END),
            (CASE WHEN SickStop > OvertimeStop THEN OvertimeStop ELSE SickStop END)
    )) / 60.0 [hours]
FROM (  
    SELECT e1.Employee, e1.START SickStart, e1.STOP SickStop,
           e2.START OvertimeStart, e2.STOP OvertimeStop 
    FROM   Exceptions e1
    JOIN   Exceptions e2 ON e1.Employee = e2.Employee
                AND (e1.START BETWEEN e2.START AND e2.STOP 
                OR e1.STOP BETWEEN e2.START AND e2.STOP)
    WHERE  e1.EXCEPTION = 'Sick'
           AND e2.EXCEPTION = 'OverTime'
) AS t
GROUP BY Employee;

edited Nov 03 '15 at 20:32

answered Nov 03 '15 at 20:25

JamieD77

13,796
1
17
27

I upvote because you tought the `minute / 60` and data range, but check my answer to see a better way to detect ranges overlap. Just want to point you dont need use subquery to calculate the `SUM`. And is easy to read if you use alias like `O, S` instead `e1, e2` – Juan Carlos Oropeza Nov 03 '15 at 22:51
Jamie, thank you for your response.. I think you and Juan kinda answered my question together... I appreciate you taking time out of your day to help a stranger. – yeahthisisrob Nov 04 '15 at 00:42

score 2 · Accepted Answer · edited May 23 '17 at 12:00

Sql Fiddle Demo

SELECT O.Employee, 
        CAST(CAST(
                DATEDIFF (
                           minute,
                           CASE WHEN S.Start < O.Start THEN O.Start
                                ELSE S.Start
                           END, 
                           CASE WHEN S.Stop > O.Stop THEN O.Stop
                                ELSE S.Stop
                           END
                         ) * 1.0 / 60 AS decimal (8,1)) as VARCHAR) + ' hours' as hours
 FROM exceptions O  -- Overtime
 JOIN exceptions S  -- Sick
   ON O.Employee = S.Employee 
  AND S.[Start] <= O.[Stop]
  AND S.[Stop] >= O.[Start]  
 WHERE 
      O.Exception  = 'OverTime'
  AND S.Exception  = 'Sick'

NOTE:

I didnt include the sum / group by so you test first if this are the value your need
Check this to Determine Whether Two Date Ranges Overlap
To test ranges outside Overtime I add
- ('Bob', 'Sick', '5:00', '7:00') doesn't show up
- ('Bob', 'Sick', '7:00', '10:00') only show 1 hour

OUTPUT

| Employee |     hours |
|----------|-----------|
|      Bob | 0.5 hours |
|      Bob | 1.0 hours |
|      Bob | 3.0 hours |
|      Jim | 2.0 hours |

EDIT I add the version with SUM

 SELECT O.Employee, 
        CAST(CAST(
                SUM(DATEDIFF (
                           minute,
                           CASE WHEN S.Start < O.Start THEN O.Start
                                ELSE S.Start
                           END, 
                           CASE WHEN S.Stop > O.Stop THEN O.Stop
                                ELSE S.Stop
                           END
                         ) * 1.0 / 60) AS decimal (8,1)) as VARCHAR) + ' hours' as hours
 FROM exceptions O  -- Overtime
 JOIN exceptions S  -- Sick
   ON O.Employee = S.Employee 
  AND S.[Start] <= O.[Stop]
  AND S.[Stop] >= O.[Start]  
 WHERE 
      O.Exception  = 'OverTime'
  AND S.Exception  = 'Sick'
 GROUP BY O.Employee

Juan! I'm so amazed you took so much of your time to help me out here... I've been at work.. I had issues with your pre-edit version but this one works great. It's very easy to read and you've explained everything so well... I'm a little blow away by how much you helped me... I only have 11/15 rep so I can't up vote you... as soon as I hit 15 you bet I am! — yeahthisisrob, Nov 04 '15 at 00:40
I just upvote your question. Btw you didnt answer what version you have? because 2012+ is better use `IIF` instead of `CASE`. Yes, writting code without debugging is like doing crossword with a pen. That is why I prepare the fiddle. — Juan Carlos Oropeza, Nov 04 '15 at 01:59

sql sum duration within start and stop time stamps

2 Answers2