The next datastructure needs to be converted to a daterange datastructure.
START_DATE END_DATE AMMOUNT
---------- ---------- ----------
01-01-2010 28-02-2010 10
01-02-2010 31-03-2010 20
01-03-2010 31-05-2010 30
01-09-2010 31-12-2010 40
Working solution:
with date_ranges
as ( select to_date('01-01-2010','dd-mm-yyyy') start_date
, to_date('28-02-2010','dd-mm-yyyy') end_date
, 10 ammount
from dual
union all
select to_date('01-02-2010','dd-mm-yyyy') start_date
, to_date('31-03-2010','dd-mm-yyyy') end_date
, 20 ammount
from dual
union all
select to_date('01-03-2010','dd-mm-yyyy') start_date
, to_date('31-05-2010','dd-mm-yyyy') end_date
, 30 ammount
from dual
union all
select to_date('01-09-2010','dd-mm-yyyy') start_date
, to_date('31-12-2010','dd-mm-yyyy') end_date
, 40 ammount
from dual
)
select rne.start_date
, lead (rne.start_date-1,1) over (order by rne.start_date) end_date
, ( select sum(dre2.ammount)
from date_ranges dre2
where rne.start_date >= dre2.start_date
and rne.start_date <= dre2.end_date
) range_ammount
from ( select dre.start_date
from date_ranges dre
union -- implicit distinct
select dre.end_date + 1
from date_ranges dre
) rne
order by rne.start_date
/
Output:
START_DATE END_DATE RANGE_AMMOUNT
---------- ---------- -------------
01-01-2010 31-01-2010 10
01-02-2010 28-02-2010 30
01-03-2010 31-03-2010 50
01-04-2010 31-05-2010 30
01-06-2010 31-08-2010
01-09-2010 31-12-2010 40
01-01-2011
7 rows selected.
However, I would like to use an analytic function to calculate the range_ammount. Is this possible?
Edited by: user5909557 on Jul 29, 2010 6:19 AM