CODEWITHSUNDEEP
sqlsql-servert-sql
yeahthisisrob
yeahthisisrob

Reputation: 81

sql sum duration within start and stop time stamps

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

Upvotes: 1

Views: 818

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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

Upvotes: 2

JamieD77
JamieD77

Reputation: 13949

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;

Upvotes: 3

Related Questions