MRPT
asked on
Query Output
Hi,
I have my source data and would need to display the output in a particular format.
I have detailed the source and output in the attached file.
Can someone please help?
Thanks
File.xlsx
I have my source data and would need to display the output in a particular format.
I have detailed the source and output in the attached file.
Can someone please help?
Thanks
File.xlsx
ASKER
Yes, using straight SQL intially. Later try to put it in OBIEE.
is your source really a table or is it the results of a query?
if a query, it would probably be better to revamp the query to return the results in the form you want.
if a query, it would probably be better to revamp the query to return the results in the form you want.
assuming your data really is coming from a table, or you if you don't have the option to redo a source query then try this...
basic idea is to pick apart all of the summations and grouping in the source and then sort and reassemble them into the form you requested.
if the data was not already summed and grouped, this could probably be done with cube and rollup grouping much easier
SELECT CASE WHEN cnt > 1 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
mgr_1 || CASE WHEN cnt > 1 AND role = 'MGR1' THEN ' Total' END mgr_1,
CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
amount rep_amount,
MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
(SELECT amount
FROM sourcetable
WHERE role = 'MGR2')
mgr_2_amount
FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
FROM sourcetable s) s
WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END
basic idea is to pick apart all of the summations and grouping in the source and then sort and reassemble them into the form you requested.
if the data was not already summed and grouped, this could probably be done with cube and rollup grouping much easier
ASKER
@sdstuber: yes, my data is really coming as a table.
I will try the query you sent.
Thanks
I will try the query you sent.
Thanks
this looks absurdly complex, but it gets very close to the expected results:
MGR_2 MGR_1 REP REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG DUKET MAGUIL1 278394 5663975 30182016
DUKET Total 278394 5663975 30182016
ABBAGG FOLKC CALLAM5 959966 4488813 30182016
ABBAGG FOLKC WALLIJ1 349777 4488813 30182016
ABBAGG FOLKC WRIGHG5 850205 4488813 30182016
FOLKC Total 2159948 4488813 30182016
ABBAGG FOLTZT HEARNT 699553 4964696 30182016
ABBAGG FOLTZT GRAFTB 328362 4964696 30182016
FOLTZT Total 1027915 4964696 30182016
ABBAGG REYNAF HARROB 765465 5303996 30182016
ABBAGG REYNAF CALDWD 856596 5303996 30182016
REYNAF Total 1622061 5303996 30182016
ABBAGG VANBRJ 6325879 6325879 30182016
and (dare I post it?), the "code"
WITH
sqf1 AS (
SELECT
mgr2.mgr_2
, mgr1.mgr_1
, rep.rep
, CASE WHEN rep.rep IS NULL THEN mgr_1_amount ELSE rep_amount END rep_amount
, mgr_1_amount
, mgr_2_amount
FROM (SELECT mgr_2, amount AS mgr_2_amount FROM source_table WHERE "RESOURCE" = mgr_2) mgr2
LEFT JOIN (SELECT mgr_2, mgr_1, amount AS mgr_1_amount FROM source_table WHERE "RESOURCE" = mgr_1) mgr1
ON mgr2.mgr_2 = mgr1.mgr_2
LEFT JOIN (SELECT mgr_1, "RESOURCE" AS rep, amount AS rep_amount FROM source_table WHERE ROLE= 'REP') rep
ON mgr1.mgr_1 = rep.mgr_1
WHERE mgr_1_amount <> mgr_2_amount
ORDER BY
mgr2.mgr_2
, mgr1.mgr_1
, rep.rep
)
, sqf2 AS (
SELECT
mgr_2
, mgr_1 || ' Total' AS label
, NULL
, sum(CASE WHEN rep IS NULL THEN mgr_1_amount ELSE rep_amount END) rep_amount
, max(mgr_1_amount)
, max(mgr_2_amount)
FROM sqf1
WHERE rep IS NOT NULL
GROUP BY GROUPING SETS (mgr_2, mgr_1)
HAVING mgr_1 IS NOT NULL
)
SELECT
*
FROM sqf1
UNION ALL
SELECT
*
FROM sqf2
ORDER BY 2
;
see it at: http://sqlfiddle.com/#!4/8dfac/3 I'm sure there has to be an easier way
easier way posted above
yes, here are the results of that, after adding to the order by
MGR_2 MGR_1 REP REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG DUKET MAGUIL1 278394 5663975 30182016
ABBAGG DUKET 5663975 5663975 30182016 -- expected total (null) & line different
ABBAGG FOLKC CALLAM5 959966 4488813 30182016
ABBAGG FOLKC WALLIJ1 349777 4488813 30182016
ABBAGG FOLKC WRIGHG5 850205 4488813 30182016
FOLKC Total 4488813 4488813 30182016 -- expected total (2159948)
ABBAGG FOLTZT HEARNT 699553 4964696 30182016
ABBAGG FOLTZT GRAFTB 328362 4964696 30182016
FOLTZT Total 4964696 4964696 30182016 -- expected total (1027915)
ABBAGG REYNAF HARROB 765465 5303996 30182016
ABBAGG REYNAF CALDWD 856596 5303996 30182016
REYNAF Total 5303996 5303996 30182016 -- expected total (1622061)
ABBAGG VANBRJ 6325879 6325879 30182016
-- the bizzarre way
MGR_2 MGR_1 REP REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
ABBAGG DUKET MAGUIL1 278394 5663975 30182016
DUKET Total 278394 5663975 30182016 -- expected total (null)
ABBAGG FOLKC CALLAM5 959966 4488813 30182016
ABBAGG FOLKC WALLIJ1 349777 4488813 30182016
ABBAGG FOLKC WRIGHG5 850205 4488813 30182016
FOLKC Total 2159948 4488813 30182016
ABBAGG FOLTZT HEARNT 699553 4964696 30182016
ABBAGG FOLTZT GRAFTB 328362 4964696 30182016
FOLTZT Total 1027915 4964696 30182016
ABBAGG REYNAF HARROB 765465 5303996 30182016
ABBAGG REYNAF CALDWD 856596 5303996 30182016
REYNAF Total 1622061 5303996 30182016
ABBAGG VANBRJ 6325879 6325879 30182016
(hence the reason for looking at it) http://sqlfiddle.com/#!4/99d5b/5
wow! thanks for the heads up.
I really wasn't paying attention to the output, I missed all sorts of things.
Fortunately, they are small, and the fixes to the original query are likewise trivial
thanks again for catching my mistakes
I really wasn't paying attention to the output, I missed all sorts of things.
Fortunately, they are small, and the fixes to the original query are likewise trivial
SELECT CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
CASE
WHEN role = 'REP'
THEN
amount
WHEN role = 'MGR1' AND cnt > 1
THEN
SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
WHEN cnt = 0
THEN
amount
END
rep_amount,
MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
(SELECT amount
FROM sourcetable
WHERE role = 'MGR2')
mgr_2_amount
FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
FROM sourcetable s) s
WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;
thanks again for catching my mistakes
SQL> SELECT CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
2 mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
3 CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
4 CASE
5 WHEN role = 'REP'
6 THEN
7 amount
8 WHEN role = 'MGR1' AND cnt > 1
9 THEN
10 SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
11 WHEN cnt = 0
12 THEN
13 amount
14 END
15 rep_amount,
16 MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1) mgr_1_amount,
17 (SELECT amount
18 FROM sourcetable
19 WHERE role = 'MGR2')
20 mgr_2_amount
21 FROM (SELECT s.*, COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt
22 FROM sourcetable s) s
23 WHERE role != 'MGR2'
24 ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;
MGR_2 MGR_1 REP REP_AMOUNT MGR_1_AMOUNT MGR_2_AMOUNT
------ ------------ ------- ---------- ------------ ------------
ABBAGG DUKET MAGUIL1 278394 5663975 30182016
DUKET Total 5663975 30182016
ABBAGG FOLKC CALLAM5 959966 4488813 30182016
ABBAGG FOLKC WALLIJ1 349777 4488813 30182016
ABBAGG FOLKC WRIGHG5 850205 4488813 30182016
FOLKC Total 2159948 4488813 30182016
ABBAGG FOLTZT GRAFTB 328362 4964696 30182016
ABBAGG FOLTZT HEARNT 699553 4964696 30182016
FOLTZT Total 1027915 4964696 30182016
ABBAGG REYNAF CALDWD 856596 5303996 30182016
ABBAGG REYNAF HARROB 765465 5303996 30182016
REYNAF Total 1622061 5303996 30182016
ABBAGG VANBRJ 6325879 6325879 30182016
13 rows selected.
SQL>
:) knew there would be a better way,
mind you I'm not a great fan of using sql for this style of presentation
- although I do use group by rollup often.
{blast, bad typo, fixed - sorry}
mind you I'm not a great fan of using sql for this style of presentation
- although I do use group by rollup often.
{blast, bad typo, fixed - sorry}
slightly more efficient version, eliminating extra io to the table
SELECT CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
CASE
WHEN role = 'REP'
THEN
amount
WHEN role = 'MGR1' AND cnt > 1
THEN
SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
WHEN cnt = 0
THEN
amount
END
rep_amount,
mgr_1_amount,
mgr_2_amount
FROM (SELECT s.*,
COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt,
MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
mgr_1_amount,
MAX(CASE WHEN role = 'MGR2' THEN amount END) OVER (PARTITION BY mgr_2) mgr_2_amount
FROM sourcetable s) s
WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;
SELECT CASE WHEN cnt > 0 AND role = 'MGR1' THEN NULL ELSE mgr_2 END mgr_2,
mgr_1 || CASE WHEN cnt > 0 AND role = 'MGR1' THEN ' Total' END mgr_1,
CASE WHEN role = 'MGR1' THEN NULL ELSE rep END rep,
CASE
WHEN role = 'REP'
THEN
amount
WHEN role = 'MGR1' AND cnt > 1
THEN
SUM(CASE WHEN role = 'REP' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
WHEN cnt = 0
THEN
amount
END
rep_amount,
mgr_1_amount,
mgr_2_amount
FROM (SELECT s.*,
COUNT(CASE WHEN role != 'MGR1' THEN 1 END) OVER (PARTITION BY mgr_2, mgr_1) cnt,
MAX(CASE WHEN role = 'MGR1' THEN amount END) OVER (PARTITION BY mgr_2, mgr_1)
mgr_1_amount,
MAX(CASE WHEN role = 'MGR2' THEN amount END) OVER (PARTITION BY mgr_2) mgr_2_amount
FROM sourcetable s) s
WHERE role != 'MGR2'
ORDER BY s.mgr_2, s.mgr_1, CASE WHEN role = 'REP' THEN 1 ELSE 2 END, rep;
v.neat, nice brief explain plan too!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
very nice indeed.
(but thinks) it would be prettier in 'comma first' layout :)
(but thinks) it would be prettier in 'comma first' layout :)
ASKER
Thank you very much.
Most tools ( even sqlplus ) have reporting capability.
Or are you looking to generate this using straight SQL?