Home » SQL & PL/SQL » SQL & PL/SQL » calculate on calculated field
calculate on calculated field Fri, 18 September 2009 06:59
 lfotopoulos Messages: 4Registered: September 2009 Junior Member
Hello,
I have the below table:

month, revenue
1, 15
2, 12
3, 14
4, 13
5, 14
6, 15

and i want to create the following:

month, revenue_forecast
1, 15
2, 12
3, 14
4, 13
5, 14
6, 15
7, =moving average of 3 previous months = (13+14+15)/3 = 14
8, =moving average of 3 previous months = (14+15+14)/3= 14.3 --based on previous calculated month and the 5,6 month
9, =moving average of 3 previous months = (15+14+14.3)/3 = 14.43 --based on previous 2 calculated months and the 6 month
10,....and so on
11, ....
12, ...

Is it possible with sql?
Thanks
Re: calculate on calculated field [message #422696 is a reply to message #422695] Fri, 18 September 2009 07:02
 ThomasG Messages: 3200Registered: April 2005 Location: Heilbronn, Germany Senior Member
Probably.

Have a look at analytical functions, lead / lag in particular.
Re: calculate on calculated field [message #422697 is a reply to message #422695] Fri, 18 September 2009 07:21
 delna.sexy Messages: 941Registered: December 2008 Location: Surat, The Diamond City Senior Member
Is it fixed that values of 6 months are given and you have to calculate for next six months?

If yes, then you can do it using MODEL clause.

regards,
Delna
Re: calculate on calculated field [message #422698 is a reply to message #422695] Fri, 18 September 2009 07:26
 Michel Cadot Messages: 64828Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Fri, 18 September 2009 07:27]

Report message to a moderator

Re: calculate on calculated field [message #422702 is a reply to message #422695] Fri, 18 September 2009 07:45
 Michel Cadot Messages: 64828Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
```drop table t ;
create table t (m integer, r integer);
insert into t values(1, 15);
insert into t values(2, 12);
insert into t values(3, 14);
insert into t values(4, 13);
insert into t values(5, 14);
insert into t values(6, 15);
commit;```

```SQL> select * from t order by 1;
M          R
---------- ----------
1         15
2         12
3         14
4         13
5         14
6         15

6 rows selected.

SQL> select m "MONTH", round(r,2) "REVENUE"
2  from (select * from t order by m)
3  model
4    dimension by (m)
5    measures (r)
6    rules upsert iterate(12)
7      (r[iteration_number+1]=nvl(r[cv()],avg(r)[m between cv()-3 and cv()-1]))
8  order by m
9  /
MONTH    REVENUE
---------- ----------
1         15
2         12
3         14
4         13
5         14
6         15
7         14
8      14.33
9      14.44
10      14.26
11      14.35
12      14.35

12 rows selected.```

Regards
Michel
Re: calculate on calculated field [message #422711 is a reply to message #422702] Fri, 18 September 2009 08:21
 lfotopoulos Messages: 4Registered: September 2009 Junior Member
Geeee, Thank you!
It works.

Re: calculate on calculated field [message #423338 is a reply to message #422702] Wed, 23 September 2009 08:23
 lfotopoulos Messages: 4Registered: September 2009 Junior Member
If i have another column-dimension like:

```drop table t ;
create table t (m integer, d varchar2(50), r integer);
insert into t values(1, 'product1', 15);
insert into t values(1, 'product2', 12);
insert into t values(2, 'product1', 14);
insert into t values(2, 'product2', 13);
insert into t values(3, 'product1', 14);
insert into t values(3, 'product2', 15);
insert into t values(4, 'product1', 15);
insert into t values(4, 'product2', 12);
insert into t values(5, 'product1', 14);
insert into t values(5, 'product2', 13);
insert into t values(6, 'product1', 14);
insert into t values(6, 'product2', 15);
commit;```

If i do this

```select m "MONTH", d "PRODUCT", round(r,2) "REVENUE"
from (select * from t order by m)
model
dimension by (m,d)
measures (r)
rules upsert iterate(12)
(r[iteration_number+1]=nvl(r[cv()],avg(r)[m between cv()-3 and cv()-1]))
order by m```

i get an "ORA-00947: not enough values error".

Any ideas how to change the rule?
I want the 3 Months Average per per product.
Thanks again.

PS: The product column values may change, i don't know the actual values when i build the query, they must be "dynamic" not "static" (if you know what i mean). In other words, i cannot reference a "cell" like r[1,'product1']

[Updated on: Wed, 23 September 2009 08:28]

Report message to a moderator

Re: calculate on calculated field [message #423342 is a reply to message #423338] Wed, 23 September 2009 08:59
 Michel Cadot Messages: 64828Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
```SQL> select m "MONTH", d "PRODUCT", round(r,2) "REVENUE"
2  from (select * from t order by d, m)
3  model
4    dimension by (m,d)
5    measures (r)
6    rules upsert all iterate(12)
7      (r[iteration_number+1, any] =
8         nvl(r[cv(),cv()],avg(r)[m between cv()-3 and cv()-1,cv()]))
9  order by d, m
10  /
MONTH PRODUCT       REVENUE
---------- ---------- ----------
1 product1           15
2 product1           14
3 product1           14
4 product1           15
5 product1           14
6 product1           14
7 product1        14.33
8 product1        14.11
9 product1        14.15
10 product1         14.2
11 product1        14.15
12 product1        14.17
1 product2           12
2 product2           13
3 product2           15
4 product2           12
5 product2           13
6 product2           15
7 product2        13.33
8 product2        13.78
9 product2        14.04
10 product2        13.72
11 product2        13.84
12 product2        13.87

24 rows selected.```

You have to read about MODEL clause if you want to use it, you cannot infer what are the syntax and keywords to use without studying the subject.

Regards
Michel
Re: calculate on calculated field [message #423420 is a reply to message #423342] Thu, 24 September 2009 01:48
 lfotopoulos Messages: 4Registered: September 2009 Junior Member
Thanks Michel,
you're right!!

I was just looking for a "quick win" solution.

Thanks again!
 Previous Topic: Complicated SQL to Calculate Prices Next Topic: Upload Word document; view contents of Word document
Goto Forum:

Current Time: Fri May 26 00:35:19 CDT 2017

Total time taken to generate the page: 0.15150 seconds