You Asked
Hi Tom:
I was wondering how we can calculate an average weekly revenue. Different clients have different rolling periods, like Client A have a 13 weeks,B have a 2 weeks.
here is an example
1) pro_customer_tb
--customer_id
--rolling_period_weeks
select * from pro_customer_tb
1,13
2,2
2)pro_charges_tb
--customer_id
--amt
--date
select * from pro_charges_tb
1,10.0,6-Jan-2003
1,2.0,7-Jan-2003
1,3.0,8-Jan-2003
1,4.0,9-Jan-2003
1,5.0,10-Jan-2003
1,10.0,13-Jan-2003
1,2.0,14-Jan-2003
1,3.0,15-Jan-2003
1,4.0,16-Jan-2003
1,5.0,17-Jan-2003
1,10.0,20-Jan-2003
1,2.0,21-Jan-2003
1,3.0,22-Jan-2003
1,4.0,23-Jan-2003
1,5.0,24-Jan-2003
January 2003
S M Tu W Th F S
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
for the week of 26th Jan to 1st Feb, if a client have a 2 weeks rolling period, i have to calculate the average weekly revenue for the period of 12 to 25th jan . similarly for 19th Jan, it is from 5th to 18th.
i couldnot think of a way using analytic function.
thanks for your help and effort
<snip>
---->still not getting it.
---->how do you know "the 26th", "the 19th"
---->i know the period is N weeks -- but how did you decide where the start points are.
---->doesn't make sense yet
<snip>
the starting point should be every week. it is like i should calculate the previous n weeks for every week.
we have 12-48 months worth of data and we should calculate for every week, the previous n weeks weekly revenue.
Let me know if this doesnot make sense yet.
and Tom said...
ops$tkyte@ORA9IR2> select * from t1;
CID WEEKS
---------- ----------
1 2
2 13
ops$tkyte@ORA9IR2> select * from t2;
CID AMT DT
---------- ---------- ---------
1 10 06-JAN-03
1 2 07-JAN-03
1 3 08-JAN-03
1 4 09-JAN-03
1 5 10-JAN-03
1 10 13-JAN-03
1 2 14-JAN-03
1 3 15-JAN-03
1 4 16-JAN-03
1 5 17-JAN-03
1 10 20-JAN-03
1 2 21-JAN-03
1 3 22-JAN-03
1 4 23-JAN-03
1 5 24-JAN-03
2 110 06-JAN-03
2 12 07-JAN-03
2 13 08-JAN-03
2 14 09-JAN-03
2 15 10-JAN-03
2 110 13-JAN-03
2 12 14-JAN-03
2 13 15-JAN-03
2 14 16-JAN-03
2 15 17-JAN-03
2 110 20-JAN-03
2 12 21-JAN-03
2 13 22-JAN-03
2 14 23-JAN-03
2 15 24-JAN-03
30 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select cid, wk, amt,
2 min(wk) over (partition by cid
3 order by wk
4 range x.days preceding) from_dt,
5 sum(amt) over (partition by cid
6 order by wk
7 range x.days preceding) sum_amt
8 from (
9 select t2.cid, next_day(t2.dt-1,'Sun') wk, sum(t2.amt) amt,
10 t1.weeks*7-0.01 days
11 from t1, t2
12 where t1.cid = t2.cid
13 group by t2.cid, next_day(t2.dt-1,'Sun'), t1.weeks
14 ) x
15 /
CID WK AMT FROM_DT SUM_AMT
---------- --------- ---------- --------- ----------
1 12-JAN-03 24 12-JAN-03 24
1 19-JAN-03 24 12-JAN-03 48
1 26-JAN-03 24 19-JAN-03 48
2 12-JAN-03 164 12-JAN-03 164
2 19-JAN-03 164 12-JAN-03 328
2 26-JAN-03 164 12-JAN-03 492
6 rows selected.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment