TSQL: Datetime overlapping and interval values

Question

Need help to create a query for this.

Start               | End
2009-01-01 06:00:00 | 2009-01-01 14:00:00
2009-01-01 06:00:00 | 2009-01-02 06:00:00
2009-01-02 07:00:00 | 2009-01-02 08:00:00
2009-01-03 06:00:00 | 2009-01-03 14:00:00
2009-01-03 09:00:00 | 2009-01-03 11:00:00
2009-01-04 22:00:00 | 2009-01-05 06:00:00
2009-01-05 01:00:00 | 2009-01-05 10:00:00

I would like to get un-overlapped time for interval (2009-01-01 00:00:00 - 2009-01-31 00:00:00) with sum for each day like this:

Date       | Duration
2009-01-01 | 18
2009-01-02 | 7
2009-01-03 | 8
2009-01-04 | 2
2009-01-05 | 10

Then looking for the total sum per whole requested interval.

Are you able to help construct this query?

Answer 1

Check out DATEDIFF in sqlserver. I'm not sure what db server you're using, but I imagine other RDBMS's would have something similar or enough that you could create your own function to do this.

SELECT start, DATEDIFF(hh, start, end) FROM ......

It looks like I missed a multiple dates portion of this. To split this by days, I think you'd almost have to process each record separately and store in a temp table or something. Maybe you could create a function that returns a table for each record that splits the durations by date and then this could be SUMMED for each date.?

Answer 2

Try this:

WITH Times AS (
        SELECT DISTINCT(Start) AS Time FROM intervals
        UNION ALL
        SELECT DISTINCT([End]) AS Time FROM intervals),
     Days AS (SELECT DISTINCT DATEADD(dd, 0, DATEDIFF(dd, 0, Time)) AS Time FROM Times),
     Times2 AS (
        SELECT Time FROM times
        UNION ALL
        SELECT Time FROM days),
     Times3 AS (SELECT ROW_NUMBER() OVER (ORDER BY Time) AS rn, Time FROM Times2),
     Times4 AS (
        SELECT T1.Time AS Start, T2.Time AS [End]
        FROM Times3 T1
        JOIN Times3 T2
        ON T1.rn + 1 = T2.rn),
     IntervalParts AS (
         SELECT DISTINCT Times4.*
         FROM Times4
         JOIN intervals
         ON Times4.Start >= intervals.Start AND Times4.[End] <= intervals.[End]),
     IntervalsByDay AS (
         SELECT 
             DATEADD(dd, 0, DATEDIFF(dd, 0, Start)) AS Day,
             DATEDIFF(hh, Start, [End]) AS Duration 
         FROM IntervalParts)
SELECT Day, SUM(Duration) AS Duration
FROM IntervalsByDay
GROUP BY Day

Results:

Day                     Duration 
2009-01-01 00:00:00.000 18
2009-01-02 00:00:00.000 7
2009-01-03 00:00:00.000 8
2009-01-04 00:00:00.000 2
2009-01-05 00:00:00.000 10

To restrict it to a certain range, just add an appropriate WHERE clause.