oracle11g - Calculating Value pro rata over months with Oracle -

Say I have a table with my user membership duration, such as:

  Product Price start_date end_date Scott T23K 250 15/01/2014 15/02/2015 Tiger T-23's 200 05/01/2014 05/02/2015 Scott T-00Z 20 10/01/2014 02/02/2015 .. .  

And I want to generate revenue reports based on my billing cycle (Monthly):

  Month product value Jan / 2014 T23K 275 January / 2014 T00Z Feb.11, 2014 T23K 275 February 2014 T-00 Z. 0.9  

I think I know that I could query, though I was thinking that there was no cunning analytic work or something that would look good on it?

I am using Oracle 11 GR2

try it, me Looks like this is your case:

  with membership ('Scott' user_, select 'T23K' product, '250' value, to_date ('15 / 01/2014 ') , 'DD / MM / YYYY') Start_date, to-date ('15 / 02/2015 ',' DD / MM / YYYY ') Select end_dates from dual union' Tiger 'user_, T23 product, '200' value, to-date ('05 / 01/2014), 'DD / MM / YYYY) start_date, TO_DATE (' 0 5 / 02/20l5 ',' DD / MM / YYYY ') dual union end_date select all' Scott 'user_ "T00Z' product, '20' value, to date (10/01/2014 ',' DD / mM / YY YY ') start_date, to_date ('02 / 02/2014', 'DD / mM / YY YY') double to END_DATE), all as Monts in (select add_months To_date ('01 / 01/2010 ',' DD / mM / YY YY '), Rovenm) My Manth, Lag (Aed_mmth (to_date ('01 / 01/2010', 'DD / mM / YY YY'), Raunm )) (Order given by RavenName) -ad_memath (to-date ('01 / 01/2010 ',' d D / MM / YY YY '), Raunmm) Level Dementl & lt; = 100 days of the month), in the form of expansion (select s. *, AllMonts.myMonth, expiry_date-start_date period, case when TRUNC (start_date, 'mon') = TRUNC (expiry_date, 'mon') then end_date- start_date) * price / (end_date- start_date) when TRUNC (start_date 'Mon') = allMonts.myMonth (add_months (allMonts.myMonth, 1) - start_date) * price / (end_date- start_date) when TRUNC (end_date, 'Mon') = allMonts.myMonth then (end_date - allMonts.myMonth) * price / (end_date- start_date) and allMonts.daymonth * price / (end_date- start_date) end member Or s the value_by_month, allMonts where allMonts.myMonth TRUNC (s.start_date, 'the Sun') and TRUNC (Skend_date, "Mon") + 1) Select myMonth, product, sum expanding group by rollup ( Value_by_month) (myMonth), product