oracle - Pro-rata volumes between a start time and an end time -

simplified, have table so:

start_date           end_date             units 06/03/2014 14:00:00  06/03/2014 15:30:00  300 

what want , have been trying find out duration (end_date - start_date). split half hour intervals , divide units equally between these intervals.

in case, duration 90 minutes, 3 half hour intervals, meaning 100 units in each interval.

from want create along these lines:

interval_date        units 06/03/2014 14:00:00  100 06/03/2014 14:30:00  100 06/03/2014 15:00:00  100 

i have got work, involved numerous nesting of sub-queries (probably clean bit use less). involved hard-coding columns interval, rather 1 column hold different intervals. example, looked this:

id  d0000  ...  d1400  d1430  d1500  d1530  d1600 ... d2330 1   0           100    100    100    0      0         0 

and reason wasn't flexible in case wanted change interval duration (15 mins, 1 hour etc).

any ideas or appreciated.

this solution covers any amount of rows (other answers cover 1 or have performance problems many rows).

that's tried:

with yourtable (select 1 id,                           to_date('06/03/2014 14:00:00','dd/mm/yyyy hh24:mi:ss') start_date,                           to_date('06/03/2014 15:30:00','dd/mm/yyyy hh24:mi:ss') end_date,                           300 units                    dual                    union                    select 2 id,                           to_date('05/03/2014 12:00:00','dd/mm/yyyy hh24:mi:ss') start_date,                           to_date('06/03/2014 23:30:15','dd/mm/yyyy hh24:mi:ss') end_date,                           1000 units                    dual),      setting (select 1/24/2 /*every half hour*/ interval dual),      borders (select /* +materialize */ min(start_date) - 1 mindate,max(end_date) + 1 maxdate yourtable),      periods (select /* +materialize */                         mindate + interval*level period_start,                         mindate + interval*(level + 1) - 1/24/60/60 /* - 1 sec */ period_end                  borders,setting                  connect level <= (maxdate - mindate)/interval) select id,        units,        units/count(*) on (partition id) periods p, yourtable y y.start_date between p.period_start , p.period_end    or p.period_start between y.start_date , y.end_date; 

where

1. yourtable should replaced table
2. setting used variable setting period, use 1/24 hour or 1/24/4 15 minutes
3. borders variable max , min dates of periods bulk
4. periods bulk of needed periods
5. and query finds intersections of calculated periods , periods in yourtable

for example returns 4 rows because 15:30 new period logically (15:29:59 work end of previous period). think can handle issue on own: requires additional information business logic.

note hint /* +materialize */ important because without oracle analyzer likes "open" subqueries connect level makes ubelievably not performant.