> 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')
)
;
