Re: best way to calculate accumulating intervals timestamps

[Richard Broersma Jr <rabroersma@xxxxxxxxx>]

> Management wants to know A+B over the course of multiple days and the start
> and finish times can occur in arbitrary times.  Any ideas for quickest way to
> solve this problem?  I know I can do it the dirty way in perl or whatever,
> but I was hoping for a solution in SQL/PLSQL.

Without using a function, you will need an auxillary table that holds calendar dates to join
against.  However, you can simulate the auxilary table by using the generate_series function. 
Also, I expect that the UNIONS can be eliminated if you use the CASE predicate to handle the
various conditions.


SELECT
        A.resource,
        B.calendardate,
        A.endtime - A.starttime AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on 
(
   (A.calendardate + '8 hours') <= A.starttime
AND
   (A.calendardate + '16 hours') >= A.enddate
)

UNION

SELECT
        A.resource,
        B.calendardate,
        A.endtime - (A.calendardate + '8 hours') AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on 
(
   A.starttime < (A.calendardate + '8 hours')
AND
   (A.calendardate + '16 hours') >= A.enddate
)

UNION

SELECT
        A.resource,
        B.calendardate,
        (A.calendardate + '16 hours') - A.starttime AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on 
(
   (A.calendardate + '8 hours') <= A.starttime
AND
    A.enddate > (A.calendardate + '16 hours')
)

UNION

SELECT
        A.resource,
        B.calendardate,
        '10 hours' AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on 
(
     A.starttime < (A.calendardate + '8 hours')
AND
     A.enddate > (A.calendardate + '16 hours')
)
;