CODEWITHSUNDEEP
sqlsql-server
Reema
Reema

Reputation: 647

Calculating total time excluding overlapped time & breaks in SQLServer

From the list of start time and end times from a select query, I need to find out the total time excluding overlapping time and breaks.

StartTime                   EndTime
2014-10-01 10:30:00.000     2014-10-01 12:00:00.000 -- 90 mins
2014-10-01 10:40:00.000     2014-10-01 12:00:00.000 --0 since its overlapped with     previous
2014-10-01 10:42:00.000     2014-10-01 12:20:00.000 -- 20 mins excluding overlapped time
2014-10-01 10:40:00.000     2014-10-01 13:00:00.000 -- 40 mins
2014-10-01 10:44:00.000     2014-10-01 12:21:00.000 -- 0 previous ones have already covered this time range
2014-10-13 15:50:00.000     2014-10-13 16:00:00.000 -- 10 mins

So the total should be 160 mins in this case.

I don't want to use so many loops to get through with this. Looking for some simple solution.

Upvotes: 1

Views: 1796

Answers (2)

Darka
Darka

Reputation: 2768

DECLARE @table TABLE (StartTime DateTime2, EndTime DateTime2)

INSERT INTO @table SELECT '2014-10-01 10:30:00.000', '2014-10-01 12:00:00.000'
INSERT INTO @table SELECT '2014-10-01 10:40:00.000', '2014-10-01 12:00:00.000'
INSERT INTO @table SELECT '2014-10-01 10:42:00.000', '2014-10-01 12:20:00.000'
INSERT INTO @table SELECT '2014-10-01 10:40:00.000', '2014-10-01 13:00:00.000'
INSERT INTO @table SELECT '2014-10-01 10:44:00.000', '2014-10-01 12:21:00.000'
INSERT INTO @table SELECT '2014-10-13 15:50:00.000', '2014-10-13 16:00:00.000'


;WITH addNR AS ( -- Add row numbers

    SELECT StartTime, EndTime, ROW_NUMBER() OVER (ORDER BY StartTime, EndTime) AS RowID
    FROM @table AS T

), createNewTable AS ( -- Recreate table according overlap time

    SELECT StartTime, EndTime, RowID 
    FROM addNR
    WHERE RowID = 1

    UNION ALL

    SELECT 
        CASE 
            WHEN a.StartTime <= AN.StartTime AND AN.StartTime <= a.EndTime THEN a.StartTime 
            ELSE AN.StartTime END AS StartTime, 
        CASE WHEN a.StartTime <= AN.EndTime AND AN.EndTime <= a.EndTime THEN a.EndTime 
            ELSE AN.EndTime END AS EndTime,
        AN.RowID 
    FROM addNR AS AN
    INNER JOIN createNewTable AS a
        ON a.RowID + 1 = AN.RowID

), getMinutes AS ( -- Get difference in minutes
    SELECT DATEDIFF(MINUTE,StartTime,MAX(EndTime)) AS diffMinutes
    FROM createNewTable
    GROUP BY StartTime
)
SELECT SUM(diffMinutes) AS Result
FROM getMinutes

And the result is 160

Upvotes: 1

LeeG
LeeG

Reputation: 728

To get the result with the data you gave, I assume that the end time is not included (otherwise it would be 91 minutes for the first run). With that in mind, this will give you the result you want with no cursors or loops. If the times span multiple days, the logic will need to be adjusted.

--Create sample data
CREATE TABLE TimesToCheck
    ([StartTime] datetime, [EndTime] datetime)
;

INSERT INTO TimesToCheck
    ([StartTime], [EndTime])
VALUES
    ('2014-10-01 10:30:00', '2014-10-01 12:00:00'),
    ('2014-10-01 10:40:00', '2014-10-01 12:00:00'),
    ('2014-10-01 10:42:00', '2014-10-01 12:20:00'),
    ('2014-10-01 10:40:00', '2014-10-01 13:00:00'),
    ('2014-10-01 10:44:00', '2014-10-01 12:21:00'),
    ('2014-10-13 15:50:00', '2014-10-13 16:00:00')
;--Now the solution.  
;WITH
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
  E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows
  N AS  (SELECT TOP (3600) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS Number FROM E4),
  TimeList AS (SELECT CAST(DATEADD(minute,n.number,0) as time) AS m FROM N), 
  --We really only need the Timelist table.  If it is already created, we can start here.
  ActiveTimes AS (SELECT DISTINCT t.m FROM TimeList T
    INNER JOIN TimesToCheck C ON t.m BETWEEN CAST(c.StartTime as time) AND CAST(DATEADD(minute,-1,c.EndTime) as time))
  SELECT COUNT(*) FROM ActiveTimes

Upvotes: 0

Related Questions