Link to home
Start Free TrialLog in
Avatar of MRPT
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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Using what tool?

Most tools ( even sqlplus ) have reporting capability.

Or are you looking to generate this using straight SQL?
Avatar of MRPT

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.
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...



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

Open in new window



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
Avatar of MRPT

ASKER

@sdstuber: yes, my data is really coming as a table.
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

Open in new window

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
;

Open in new window

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

Open in new window

 (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

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;

Open in new window


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>

Open in new window

:) 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}
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;
v.neat, nice brief explain plan too!
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
very nice indeed.
(but thinks) it would be prettier in 'comma first' layout :)
Avatar of MRPT

ASKER

Thank you very much.