Calculating cost over several months with Oracle
Let's say I have a table that contains the term of my user followers, for example:
user product value start_date end_date
Scott T23K 250 15/01/2014 15/02/2015
Tiger T23K 200 05/01/2014 05/02/2015
Scott T00Z 20 10/01/2014 02/02/2015
...
And I want to generate an income statement based on my billing cycles (monthly) that looks like this:
month product value
Jan/2014 T23K 275
Jan/2014 T00Z 19.1
Feb/2014 T23K 275
Feb/2014 T00Z 0.9
I guess I could query like this , although I was wondering if there was a smart analytic function or something that looked better. Ideas?
I am using Oracle 11gR2
+3
source to share
1 answer
Try it, I think this suits your case:
with subscriptions as
(
select 'Scott' user_, 'T23K' product , '250' value , to_date('15/01/2014','DD/MM/YYYY') start_date , to_date('15/02/2015','DD/MM/YYYY') end_date from dual
union all
select 'Tiger' user_, 'T23K' product , '200' value , to_date('05/01/2014','DD/MM/YYYY') start_date , to_date('05/02/2015','DD/MM/YYYY') end_date from dual
union all
select 'Scott' user_, 'T00Z' product , '20' value , to_date('10/01/2014','DD/MM/YYYY') start_date , to_date('02/02/2014','DD/MM/YYYY') end_date from dual
)
, allMonts as
(select add_months(to_date('01/01/2010','DD/MM/YYYY'),rownum) myMonth , lag(add_months(to_date('01/01/2010','DD/MM/YYYY'),rownum)) over (order by rownum desc) -add_months(to_date('01/01/2010','DD/MM/YYYY'),rownum) daymonth from dual CONNECT BY LEVEL <= 100
)
, detail as
(
select s.*,allMonts.myMonth, end_date-start_date duration,
case when trunc(start_date,'MON')=trunc(end_date,'MON') then (end_date-start_date)*value/( end_date-start_date)
when trunc(start_date,'MON')=allMonts.myMonth then (add_months(allMonts.myMonth,1) - start_date)*value/( end_date-start_date)
when trunc(end_date,'MON')=allMonts.myMonth then (end_date - allMonts.myMonth )*value/( end_date-start_date)
else allMonts.daymonth*value/( end_date-start_date)
end value_by_month
from subscriptions s , allMonts
where allMonts.myMonth
between trunc(s.start_date,'MON') and trunc(s.end_date,'MON')+1
)
select myMonth, product, sum(value_by_month)
from detail
group by rollup(myMonth), product
+1
source to share