CODEWITHSUNDEEP
sqltime
user2502961
user2502961

Reputation: 157

How to get difference between start and end time with duration?

I have a table in which each row has StartTime, EndTime and duration with other columns, for example:

Unique_ID        SartTime             EndTime         Duration 
    1       08:00:00.0000000      12:00:00.0000000      10

I need to write a query which returns result something similar to: 

Unique_ID        SartTime             EndTime         Duration 
    1       08:00:00.0000000      08:10:00.0000000      10 
    1       08:10:00.0000000      08:20:00.0000000      10
    1       08:20:00.0000000      08:30:00.0000000      10  
and so on... till
    1       11:50:00.0000000     12:00:00.0000000       10

Thanks in advance:)

Upvotes: 1

Views: 1206

Answers (2)

dnoeth
dnoeth

Reputation: 60462

If your DBMS doesn't support recursion you can utilize a cross join to a table with numbers in it. I use values between 0 and 1439 because a day has 1440 minutes, of course you should decrease this based on your data). This is pure Standard SQL:

SELECT Unique_ID
  ,StartTime + (n * INTERVAL '10' MINUTE)
  ,CASE 
      WHEN StartTime + ((n +1) * INTERVAL '10' MINUTE) < EndTime 
      THEN StartTime + ((n +1) * INTERVAL '10' MINUTE)
      ELSE EndTime
   end
FROM vt
CROSS JOIN
  (SELECT n FROM number_table WHERE n BETWEEN 0 AND 1439) AS numbers
WHERE StartTime + (n * INTERVAL '10' MINUTE) >= StartTime
  AND StartTime + (n * INTERVAL '10' MINUTE) <  EndTime
ORDER BY 1,2

Upvotes: 0

Stephan
Stephan

Reputation: 6018

I changed your data to show that the duration can be different, and that the query works with multiple rows. Also I changed it to make the results shorter.

DECLARE @yourTable TABLE (Unique_ID INT, StartTime TIME, EndTime TIME, Duration INT);
INSERT INTO @yourTable
VALUES  (1,'08:00:00.0000000','8:30:00.0000000',10),
        (2,'06:00:00.0000000','7:00:00.0000000',30);

;WITH CTE_Recursion 
AS
(
    SELECT  Unique_ID,
            StartTime,
            DATEADD(MINUTE,Duration,StartTime) NextTime,
            EndTime,
            Duration
    FROM @yourTable

    UNION ALL

    SELECT Unique_ID, DATEADD(MINUTE,Duration,StartTime),DATEADD(MINUTE,Duration,NextTime),EndTime,Duration
    FROM CTE_Recursion
    WHERE DATEADD(MINUTE,Duration,StartTime) < EndTime
)

SELECT Unique_ID,StartTime,NextTime,Duration
FROM CTE_Recursion
ORDER BY Unique_ID,StartTime

Results:

Unique_ID   StartTime        NextTime         Duration
----------- ---------------- ---------------- -----------
1           08:00:00.0000000 08:10:00.0000000 10
1           08:10:00.0000000 08:20:00.0000000 10
1           08:20:00.0000000 08:30:00.0000000 10
2           06:00:00.0000000 06:30:00.0000000 30
2           06:30:00.0000000 07:00:00.0000000 30

Upvotes: 1

Related Questions