Home » Developer & Programmer » Reports & Discoverer » Accumlulating totals as QTD, YTD for discoverer
| Accumlulating totals as QTD, YTD for discoverer [message #160646] |
Mon, 27 February 2006 21:42  |
BexOne
Messages: 10
Registered: August 2005
|
Junior Member |
|
|
Hi,
My data is kept monthly as follows
key1,key2,year,month,data1,data2,data3. For discovere we need to summarize to QTD and YTD values.
I have tried assigning quarter indice eg. Jan = 1, Feb=1, Jul=3 for QTD, but there must be a more practical approach.
Secondly is it possible by any means for calculated items in one folder to refer to other folder items for calculations purpose. Is there any special consideration for creating calculated items in discovere vs calculated items in materialized view. My discovere folders are based on mviews.
Best Regards,
BexOne.
|
|
|
|
| Re: Accumlulating totals as QTD, YTD for discoverer [message #160688 is a reply to message #160646] |
Tue, 28 February 2006 01:35  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Using TO_CHAR, you can translate a month into a quarter:
SQL> COL QUARTER FORMAT A10
SQL> SELECT TO_CHAR(TO_DATE('&themonth','mon'),'Q') Quarter
2 FROM dual
3 /
Enter value for themonth: jan
old 1: SELECT TO_CHAR(TO_DATE('&themonth','mon'),'Q') Quarter
new 1: SELECT TO_CHAR(TO_DATE('jan','mon'),'Q') Quarter
QUARTER
----------
1
SQL> /
Enter value for themonth: mar
old 1: SELECT TO_CHAR(TO_DATE('&themonth','mon'),'Q') Quarter
new 1: SELECT TO_CHAR(TO_DATE('mar','mon'),'Q') Quarter
QUARTER
----------
1
SQL> /
Enter value for themonth: aug
old 1: SELECT TO_CHAR(TO_DATE('&themonth','mon'),'Q') Quarter
new 1: SELECT TO_CHAR(TO_DATE('aug','mon'),'Q') Quarter
QUARTER
----------
3
SQL> /
Enter value for themonth: dec
old 1: SELECT TO_CHAR(TO_DATE('&themonth','mon'),'Q') Quarter
new 1: SELECT TO_CHAR(TO_DATE('dec','mon'),'Q') Quarter
QUARTER
----------
4
SQL>
MHE
|
|
|
|
| Re: Accumlulating totals as QTD, YTD for discoverer [message #160703 is a reply to message #160688] |
Tue, 28 February 2006 02:36 |
BexOne
Messages: 10
Registered: August 2005
|
Junior Member |
|
|
Thanks Maher,
But i am looking for calculating and getting single row values. I'll summarize again:
Current Data:
SECTION(VARCHAR) STORE(VC) BUSTYPE(VC) YEAR(NUM) MONTH(NUM), MTDAMT, MTDNOS
10 22 22310 2004 04 120000 117
10 22 22334 2004 05 150000 120
10 22 22310 2004 05 100000 110
11 22 22310 2004 07 120000 117
11 22 22334 2004 06 200000 180
11 22 22334 2004 08 150000 120
Expected Result for mview:
SEC STORE BUSTYPE YEAR MONTH MTDAMT QTD_MTDAMT QTD_MTDNOS YTD_MTDAMT YTD_MTDNOS
mtd qtdamt qtdnos ytdamt ytdnos
10 22 22310 2004 04 120000 120000 117 120000 117
10 22 22310 2004 05 100000 220000 227 220000 227
10 22 22334 2004 05 150000 150000 120 150000 120
11 22 22310 2004 07 120000 120000 117 120000 117
11 22 22334 2004 06 200000 200000 180 200000 180
11 22 22334 2004 08 150000 150000 120 150000 120
Pls. elaborate what functions or timefunction tables can be used.
|
|
|
|
| Re: Accumlulating totals as QTD, YTD for discoverer [message #160721 is a reply to message #160703] |
Tue, 28 February 2006 03:25 |
|
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
I fail to see the difference between the QTD and the YTD columns. Are you sure of the desired result? And can you please format it, this is not very readable. Anyway this is what I came up with:
SQL> CREATE TABLE mhe_foo( section VARCHAR2(10)
2 , store VARCHAR2(10)
3 , bustype VARCHAR2(10)
4 , year NUMBER
5 , month NUMBER
6 , mtdamt NUMBER
7 , mtdnos NUMBER
8 )
9 /
Table created.
SQL>
SQL> INSERT INTO mhe_foo VALUES('10', '22', '22310', 2004, 04, 120000, 117)
2 /
1 row created.
SQL> INSERT INTO mhe_foo VALUES('10', '22', '22334', 2004, 05, 150000, 120)
2 /
1 row created.
SQL> INSERT INTO mhe_foo VALUES('10', '22', '22310', 2004, 05, 100000, 110)
2 /
1 row created.
SQL> INSERT INTO mhe_foo VALUES('11', '22', '22310', 2004, 07, 120000, 117)
2 /
1 row created.
SQL> INSERT INTO mhe_foo VALUES('11', '22', '22334', 2004, 06, 200000, 180)
2 /
1 row created.
SQL> INSERT INTO mhe_foo VALUES('11', '22', '22334', 2004, 08, 150000, 120)
2 /
1 row created.
SQL>
SQL> COL section FORMAT A7
SQL> COL store FORMAT A5
SQL> COL bustype FORMAT A7
SQL> COL year FORMAT 9999
SQL> COL month FORMAT 99999
SQL>
SQL> SELECT section
2 , store
3 , bustype
4 , year
5 , month
6 , mtdamt
7 , mtdnos
8 FROM mhe_foo
9 /
SECTION STORE BUSTYPE YEAR MONTH MTDAMT MTDNOS
------- ----- ------- ----- ------ ---------- ----------
10 22 22310 2004 4 120000 117
10 22 22334 2004 5 150000 120
10 22 22310 2004 5 100000 110
11 22 22310 2004 7 120000 117
11 22 22334 2004 6 200000 180
11 22 22334 2004 8 150000 120
6 rows selected.
SQL>
SQL> SELECT section
2 , store
3 , bustype
4 , year
5 , month
6 , SUM(mtdamt) OVER ( PARTITION BY section
7 , store
8 , bustype
9 , year,TO_CHAR(TO_DATE(month,'mm'),'Q')
10 ORDER BY year
11 , month
12 ) qtd_mtdamt
13 , SUM(mtdnos) OVER ( PARTITION BY section
14 , store
15 , bustype
16 , year,TO_CHAR(TO_DATE(month,'mm'),'Q')
17 ORDER BY year
18 , month
19 ) qtd_mtdnos
20 , SUM(mtdamt) OVER ( PARTITION BY section
21 , store
22 , bustype
23 , year
24 ORDER BY year
25 , month
26 ) ytd_mtdamt
27 , SUM(mtdnos) OVER ( PARTITION BY section
28 , store
29 , bustype
30 , year
31 ORDER BY year
32 , month
33 ) ytd_mtdnos
34 FROM mhe_foo
35 ORDER BY section
36 , store
37 , bustype
38 , year
39 , month
40 /
SECTION STORE BUSTYPE YEAR MONTH QTD_MTDAMT QTD_MTDNOS YTD_MTDAMT YTD_MTDNOS
------- ----- ------- ----- ------ ---------- ---------- ---------- ----------
10 22 22310 2004 4 120000 117 120000 117
10 22 22310 2004 5 220000 227 220000 227
10 22 22334 2004 5 150000 120 150000 120
11 22 22310 2004 7 120000 117 120000 117
11 22 22334 2004 6 200000 180 200000 180
11 22 22334 2004 8 150000 120 350000 300
6 rows selected.
SQL>
SQL> DROP TABLE mhe_foo
2 /
Table dropped.
SQL>
MHE
|
|
|
|
| Re: Accumlulating totals as QTD, YTD for discoverer [message #162683 is a reply to message #160721] |
Mon, 13 March 2006 00:54 |
BexOne
Messages: 10
Registered: August 2005
|
Junior Member |
|
|
Thanks Maher,
The scripts works fine. Additional script to achieve the result.
SELECT
SUM(SUM(DECODE(TRUNC(( TO_NUMBER(SUBSTR(A.TIME_SEQ,5,2))-1 )/3)+1,
1,NVL(A.QTD_INCOME,0),
2,NVL(A.QTD_INCOME,0),
3,NVL(A.QTD_INCOME,0),
4,NVL(A.QTD_INCOME,0),0))) OVER(PARTITION BY A.OU_CODE, A.STORE_SEQ, A.BUS_SUBTYPE_CODE,
TO_NUMBER(SUBSTR(A.TIME_SEQ,1,4)),TRUNC((TO_NUMBER(SUBSTR(A.TIME_SEQ,5,2))-1 )/3)+1
ORDER BY TO_NUMBER(SUBSTR(A.TIME_SEQ,5,2))) AS QTD_INCOME, -- QTD Income
SUM(NVL(SUM(A.YTD_INCOME),0)) OVER(PARTITION BY A.OU_CODE, A.STORE_SEQ, A.BUS_SUBTYPE_CODE,
TO_NUMBER(SUBSTR(A.TIME_SEQ,1,4)) ORDER BY TO_NUMBER(SUBSTR(A.TIME_SEQ,5,2))) AS YTD_INCOME, -- YTD Income
FROM MONTHLY_INCOME......
Regards,
BexOne.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 10:11:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
