Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cube/Rollup Help

Status
Not open for further replies.
fmrock

fmrock

Programmer
Sep 5, 2006
510
US
I have the following Query

Code: 
SELECT 
	C.CPCODE "Group_Num"
	, L.LOCNAME "Site_Name"	
	,FC.CDESC "FC_Desc"
	,add_months(trunc(sysdate,'MONTH'),-12) "dtMonth -12"	
	,add_months(trunc(sysdate,'MONTH'),-11) "dtMonth -11"	
	,add_months(trunc(sysdate,'MONTH'),-10) "dtMonth -10"
	,add_months(trunc(sysdate,'MONTH'),-9) "dtMonth -9"
	,add_months(trunc(sysdate,'MONTH'),-8) "dtMonth -8"
	,add_months(trunc(sysdate,'MONTH'),-7) "dtMonth -7"	
	,add_months(trunc(sysdate,'MONTH'),-6) "dtMonth -6"	
	,add_months(trunc(sysdate,'MONTH'),-5) "dtMonth -5"	
	,add_months(trunc(sysdate,'MONTH'),-4) "dtMonth -4"	
	,add_months(trunc(sysdate,'MONTH'),-3) "dtMonth -3"
	,add_months(trunc(sysdate,'MONTH'),-2) "dtMonth -2"	
	,add_months(trunc(sysdate,'MONTH'),-1) "dtMonth -1"
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-12)) THEN C.AMOUNT ELSE 0 END) "Month -12"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-11)) THEN C.AMOUNT ELSE 0 END) "Month -11"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-10)) THEN C.AMOUNT ELSE 0 END) "Month -10"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-9)) THEN C.AMOUNT ELSE 0 END) "Month -9"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-8)) THEN C.AMOUNT ELSE 0 END) "Month -8"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-7)) THEN C.AMOUNT ELSE 0 END) "Month -7"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-6)) THEN C.AMOUNT ELSE 0 END) "Month -6"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-5)) THEN C.AMOUNT ELSE 0 END) "Month -5"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-4)) THEN C.AMOUNT ELSE 0 END) "Month -4"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-3)) THEN C.AMOUNT ELSE 0 END) "Month -3"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-2)) THEN C.AMOUNT ELSE 0 END) "Month -2"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-1)) THEN C.AMOUNT ELSE 0 END) "Month -1"		
	,Sum(C.AMOUNT) "Total_Pmts"
	,add_months(trunc(sysdate,'MONTH'),-12) "Start_Date"
	,(trunc(sysdate,'MONTH')-1) "End_Date"
FROM 
	MEDCHARGES C
	LEFT JOIN MEDLOCATIONS L ON ((C.CPCODE = L.LOCCPCODE) AND (L.LOCCODE=C.DOCLOC))
	LEFT JOIN FINANCIALCODES FC ON ((FC.FCCODE=C.CPCODE) AND (FC.CODES=C.BILLSTATUS))
WHERE
	(C.CPCODE IN ('1','2','3'))
	AND (C.TYPE='P') 
	AND (C.SPLITFLAG IS NULL)	
	AND (C.POSTDATE between to_Number(to_char(add_months(trunc(sysdate,'MONTH'),-12),'J')) and to_Number(to_char((trunc(sysdate,'MONTH')-1),'J')))	
GROUP BY 
	C.CPCODE
	, L.LOCNAME
	,FC.CDESC

Right now this query pulls back 3 locations and summarizes it by Financial Class. I would like a 4th section that pulls is a summary of all 3 locations by Financial class.

I have tried a few things with cube with no luck. Any help or direction would be great.
 
Sort by date Sort by votes
Without knowing your database inside out, it's difficult to mimic this query, so I've attempted to create a simplified example. From that it looks as if a simple rollup ought to do what you want e.g.

Code: 
SQL> create table medcharges (locname varchar2(20), postdate date, amount number)
Table created.
SQL> insert into medcharges values ('LOC1', add_months(trunc(sysdate, 'MONTH'), -1), 10)
1 row created.
SQL> insert into medcharges values ('LOC1', add_months(trunc(sysdate, 'MONTH'), -1), 17)
1 row created.
SQL> insert into medcharges values ('LOC1', add_months(trunc(sysdate, 'MONTH'), -1), 19)
1 row created.
SQL> insert into medcharges values ('LOC1', add_months(trunc(sysdate, 'MONTH'), -2), 11)
1 row created.
SQL> insert into medcharges values ('LOC1', add_months(trunc(sysdate, 'MONTH'), -3), 15)
1 row created.
SQL> insert into medcharges values ('LOC2', add_months(trunc(sysdate, 'MONTH'), -1), 25)
1 row created.
SQL> insert into medcharges values ('LOC2', add_months(trunc(sysdate, 'MONTH'), -2), 20)
1 row created.
SQL> insert into medcharges values ('LOC2', add_months(trunc(sysdate, 'MONTH'), -3), 22)
1 row created.
SQL> insert into medcharges values ('LOC2', add_months(trunc(sysdate, 'MONTH'), -3), 6)
1 row created.
SQL> insert into medcharges values ('LOC3', add_months(trunc(sysdate, 'MONTH'), -3),33)
1 row created.
SQL> select locname, 
         sum(case when postdate = add_months(trunc(sysdate, 'MONTH'), -1) then amount end) as month1_amt,
         sum(case when postdate = add_months(trunc(sysdate, 'MONTH'), -2) then amount end) as month2_amt,
         sum(case when postdate = add_months(trunc(sysdate, 'MONTH'), -3) then amount end) as month3_amt
from medcharges
group by rollup(locname)

LOCNAME              MONTH1_AMT MONTH2_AMT MONTH3_AMT
-------------------- ---------- ---------- ----------
LOC1                         46         11         15
LOC2                         25         20         28
LOC3                                               33
                             71         31         76

4 rows selected.

What actually goes wrong when you try this?

For Oracle-related work, contact me through Linked-In.
 
Upvote 0 Downvote
Thanks Dagon,

It only shows an over all grand total. And not the totals by fc_desc for all 3 locations.

This query actaully gives me the data the way I want, but I dont think this is really the best way.

Code: 
SELECT 
	C.CPCODE "Group_Num"
	, L.LOCNAME "Site_Name"	
	,FC.CDESC "FC_Desc"
	,add_months(trunc(sysdate,'MONTH'),-12) "dtMonth -12"	
	,add_months(trunc(sysdate,'MONTH'),-11) "dtMonth -11"	
	,add_months(trunc(sysdate,'MONTH'),-10) "dtMonth -10"
	,add_months(trunc(sysdate,'MONTH'),-9) "dtMonth -9"
	,add_months(trunc(sysdate,'MONTH'),-8) "dtMonth -8"
	,add_months(trunc(sysdate,'MONTH'),-7) "dtMonth -7"	
	,add_months(trunc(sysdate,'MONTH'),-6) "dtMonth -6"	
	,add_months(trunc(sysdate,'MONTH'),-5) "dtMonth -5"	
	,add_months(trunc(sysdate,'MONTH'),-4) "dtMonth -4"	
	,add_months(trunc(sysdate,'MONTH'),-3) "dtMonth -3"
	,add_months(trunc(sysdate,'MONTH'),-2) "dtMonth -2"	
	,add_months(trunc(sysdate,'MONTH'),-1) "dtMonth -1"
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-12)) THEN C.AMOUNT ELSE 0 END) "Month -12"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-11)) THEN C.AMOUNT ELSE 0 END) "Month -11"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-10)) THEN C.AMOUNT ELSE 0 END) "Month -10"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-9)) THEN C.AMOUNT ELSE 0 END) "Month -9"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-8)) THEN C.AMOUNT ELSE 0 END) "Month -8"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-7)) THEN C.AMOUNT ELSE 0 END) "Month -7"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-6)) THEN C.AMOUNT ELSE 0 END) "Month -6"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-5)) THEN C.AMOUNT ELSE 0 END) "Month -5"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-4)) THEN C.AMOUNT ELSE 0 END) "Month -4"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-3)) THEN C.AMOUNT ELSE 0 END) "Month -3"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-2)) THEN C.AMOUNT ELSE 0 END) "Month -2"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-1)) THEN C.AMOUNT ELSE 0 END) "Month -1"		
	,Sum(C.AMOUNT) "Total_Pmts"
	,add_months(trunc(sysdate,'MONTH'),-12) "Start_Date"
	,(trunc(sysdate,'MONTH')-1) "End_Date"
FROM 
	MEDCHARGES C
	LEFT JOIN MEDLOCATIONS L ON ((C.CPCODE = L.LOCCPCODE) AND (L.LOCCODE=C.DOCLOC))
	LEFT JOIN FINANCIALCODES FC ON ((FC.FCCODE=C.CPCODE) AND (FC.CODES=C.BILLSTATUS))
WHERE
	(C.CPCODE IN ('1','2','3'))
	AND (C.TYPE='P') 
	AND (C.SPLITFLAG IS NULL)	
	AND (C.POSTDATE between to_Number(to_char(add_months(trunc(sysdate,'MONTH'),-12),'J')) and to_Number(to_char((trunc(sysdate,'MONTH')-1),'J')))	
GROUP BY 
	C.CPCODE
	, L.LOCNAME
	,FC.CDESC 
	
/*----------------------------------------------------------------------------------------------*/
UNION ALL
/*----------------------------------------------------------------------------------------------*/
SELECT 
	'ALL' "Group_Num"
	,'SIH Consolidated' "Site_Name"	
	,FC.CDESC "FC_Desc"
	,add_months(trunc(sysdate,'MONTH'),-12) "dtMonth -12"	
	,add_months(trunc(sysdate,'MONTH'),-11) "dtMonth -11"	
	,add_months(trunc(sysdate,'MONTH'),-10) "dtMonth -10"
	,add_months(trunc(sysdate,'MONTH'),-9) "dtMonth -9"
	,add_months(trunc(sysdate,'MONTH'),-8) "dtMonth -8"
	,add_months(trunc(sysdate,'MONTH'),-7) "dtMonth -7"	
	,add_months(trunc(sysdate,'MONTH'),-6) "dtMonth -6"	
	,add_months(trunc(sysdate,'MONTH'),-5) "dtMonth -5"	
	,add_months(trunc(sysdate,'MONTH'),-4) "dtMonth -4"	
	,add_months(trunc(sysdate,'MONTH'),-3) "dtMonth -3"
	,add_months(trunc(sysdate,'MONTH'),-2) "dtMonth -2"	
	,add_months(trunc(sysdate,'MONTH'),-1) "dtMonth -1"
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-12)) THEN C.AMOUNT ELSE 0 END) "Month -12"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-11)) THEN C.AMOUNT ELSE 0 END) "Month -11"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-10)) THEN C.AMOUNT ELSE 0 END) "Month -10"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-9)) THEN C.AMOUNT ELSE 0 END) "Month -9"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-8)) THEN C.AMOUNT ELSE 0 END) "Month -8"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-7)) THEN C.AMOUNT ELSE 0 END) "Month -7"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-6)) THEN C.AMOUNT ELSE 0 END) "Month -6"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-5)) THEN C.AMOUNT ELSE 0 END) "Month -5"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-4)) THEN C.AMOUNT ELSE 0 END) "Month -4"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-3)) THEN C.AMOUNT ELSE 0 END) "Month -3"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-2)) THEN C.AMOUNT ELSE 0 END) "Month -2"		
	,SUM(CASE WHEN (trunc(to_date(C.POSTDATE,'j'),'MONTH')=add_months(trunc(sysdate,'MONTH'),-1)) THEN C.AMOUNT ELSE 0 END) "Month -1"		
	,Sum(C.AMOUNT) "Total_Pmts"
	,add_months(trunc(sysdate,'MONTH'),-12) "Start_Date"
	,(trunc(sysdate,'MONTH')-1) "End_Date"
FROM 
	MEDCHARGES C
	LEFT JOIN MEDLOCATIONS L ON ((C.CPCODE = L.LOCCPCODE) AND (L.LOCCODE=C.DOCLOC))
	LEFT JOIN FINANCIALCODES FC ON ((FC.FCCODE=C.CPCODE) AND (FC.CODES=C.BILLSTATUS))
WHERE
	(C.CPCODE IN ('1','2',''))
	AND (C.TYPE='P') 
	AND (C.SPLITFLAG IS NULL)	
	AND (C.POSTDATE between to_Number(to_char(add_months(trunc(sysdate,'MONTH'),-12),'J')) and to_Number(to_char((trunc(sysdate,'MONTH')-1),'J')))	
GROUP BY
	'ALL' 
	,'Consolidated' 
	,FC.CDESC
 
Upvote 0 Downvote
Extending my example, you could try something like this:

Code: 
SQL> create table medcharges (fc_desc varchar2(10), locname varchar2(20), postdate date, amount number)
Table created.
SQL> insert into medcharges values ('FC1', 'LOC1', add_months(trunc(sysdate, 'MONTH'), -1), 10)
1 row created.
SQL> insert into medcharges values ('FC1', 'LOC1', add_months(trunc(sysdate, 'MONTH'), -1), 17)
1 row created.
SQL> insert into medcharges values ('FC1', 'LOC1', add_months(trunc(sysdate, 'MONTH'), -1), 19)
1 row created.
SQL> insert into medcharges values ('FC3', 'LOC1', add_months(trunc(sysdate, 'MONTH'), -2), 11)
1 row created.
SQL> insert into medcharges values ('FC1', 'LOC1', add_months(trunc(sysdate, 'MONTH'), -3), 15)
1 row created.
SQL> insert into medcharges values ('FC1', 'LOC2', add_months(trunc(sysdate, 'MONTH'), -1), 25)
1 row created.
SQL> insert into medcharges values ('FC2', 'LOC2', add_months(trunc(sysdate, 'MONTH'), -2), 20)
1 row created.
SQL> insert into medcharges values ('FC2', 'LOC2', add_months(trunc(sysdate, 'MONTH'), -3), 22)
1 row created.
SQL> insert into medcharges values ('FC1', 'LOC2', add_months(trunc(sysdate, 'MONTH'), -3), 6)
1 row created.
SQL> insert into medcharges values ('FC1', 'LOC3', add_months(trunc(sysdate, 'MONTH'), -3),33)
1 row created.
SQL> select fc_desc,
         locname, 
         sum(case when postdate = add_months(trunc(sysdate, 'MONTH'), -1) then amount end) as month1_amt,
         sum(case when postdate = add_months(trunc(sysdate, 'MONTH'), -2) then amount end) as month2_amt,
         sum(case when postdate = add_months(trunc(sysdate, 'MONTH'), -3) then amount end) as month3_amt
from medcharges
group by fc_desc, rollup(locname)
order by fc_desc, locname nulls last

FC_DESC    LOCNAME              MONTH1_AMT MONTH2_AMT MONTH3_AMT
---------- -------------------- ---------- ---------- ----------
FC1        LOC1                         46                    15
FC1        LOC2                         25                     6
FC1        LOC3                                               33
FC1                                     71                    54
FC2        LOC2                                    20         22
FC2                                                20         22
FC3        LOC1                                    11           
FC3                                                11


For Oracle-related work, contact me through Linked-In.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sqlPower
  • Locked
  • Question
Help with Integer to Char to Datetime stamp
Replies
2
Views
727
SkipVought
S
Tsmtbo2011
  • Locked
  • Question
Help Needed!! Micros WS6 610
Replies
1
Views
189
carp
carp
ssmith999
  • Locked
  • Question
Query help w/ fiscal calendar query
Replies
1
Views
136
johnherman
johnherman
DABETTIE
  • Locked
  • Question
Please Help! Lowered my manager privileges
Replies
0
Views
113
DABETTIE
DABETTIE
BeeBear2
  • Locked
  • Question
Help Converting Datetime field in Oracle/Sql Stored Proc for passing a Date Parameter
Replies
3
Views
242
stefanhei
stefanhei

Part and Inventory Search

Sponsor

Back
Top