SELECT BUSINESS LINE, PILLAR, DEPARTMENT, GROUP NAME, SECTION NAME, RESOURCE NAME, BUSINESS FUNCTION, INVESTMENT ID, INVESTMENT NAME, PHASE, ACTIVITY, TASK, RECORD TYPE, SUM(JAN 22) JAN, SUM(FEB 22) FEB, SUM(MAR 22) MAR, SUM(APR 22) APR, SUM(MAY 22) MAY, SUM(JUN 22) JUN, SUM(JUL 22) JUL, SUM(AUG 22) AUG, SUM(SEP 22) SEP, SUM(OCT 22) OCT, SUM(NOV 22) NOV, SUM(DEC 22) DEC, SUM(TOTAL) TOTAL FROM (SELECT OBS.BUSINESS LINE, OBS.PILLAR, OBS.DEPARTMENT, OBS.GROUP NAME, OBS.SECTION NAME, RESR.RESOURCE NAME, MPJ.BUSINESS FUNCTION, INV.INVESTMENT ID, INV.INVESTMENT NAME, TSK1.TASK NAME PHASE, TSK2.TASK NAME ACTIVITY, TSK3.TASK NAME TASK, 'Actuals' RECORD TYPE, SUM(CASE WHEN CMP.MONTH START DATE = '01-JAN-22' THEN PF.ACTUAL HOURS ELSE 0 END) JAN 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-FEB-22' THEN PF.ACTUAL HOURS ELSE 0 END) FEB 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-MAR-22' THEN PF.ACTUAL HOURS ELSE 0 END) MAR 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-APR-22' THEN PF.ACTUAL HOURS ELSE 0 END) APR 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-MAY-22' THEN PF.ACTUAL HOURS ELSE 0 END) MAY 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-JUN-22' THEN PF.ACTUAL HOURS ELSE 0 END) JUN 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-JUL-22' THEN PF.ACTUAL HOURS ELSE 0 END) JUL 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-AUG-22' THEN PF.ACTUAL HOURS ELSE 0 END) AUG 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-SEP-22' THEN PF.ACTUAL HOURS ELSE 0 END) SEP 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-OCT-22' THEN PF.ACTUAL HOURS ELSE 0 END) OCT 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-NOV-22' THEN PF.ACTUAL HOURS ELSE 0 END) NOV 22, SUM(CASE WHEN CMP.MONTH START DATE = '01-DEC-22' THEN PF.ACTUAL HOURS ELSE 0 END) DEC 22, SUM(PF.ACTUAL HOURS) TOTAL FROM PPM DWH.DWH INV INVESTMENT INV, PPM DWH.DWH INV ASSIGNMENT INVA, PPM DWH.DWH INV TEAM TM, PPM DWH.DWH RES RESOURCE RESR, PPM DWH.DWH INV TASK TSK1, PPM DWH.DWH INV TASK HIERARCHY TSKPA, PPM DWH.DWH INV TASK TSK2, PPM DWH.DWH INV TASK HIERARCHY TSKAT, PPM DWH.DWH INV TASK TSK3, PPM DWH.DWH INV ASSIGN PERIOD FACTS PF, PPM DWH.DWH CMN PERIOD CMP, (SELECT DISTINCT OM.RESOURCE KEY, OM.OBS LEVEL2 AS BUSINESS LINE, OM.OBS LEVEL3 AS PILLAR, OM.OBS LEVEL4 AS DEPARTMENT, OM.OBS LEVEL5 AS GROUP NAME, OM.OBS LEVEL6 AS SECTION NAME FROM PPM DWH.DWH RES OBS MAPPING OM WHERE OM.OBS TYPE KEY = 5000011 ORDER BY 3 ASC, 4 ASC, 5 ASC) OBS, (SELECT DISTINCT OM.OBS TYPE KEY, OM.INVESTMENT KEY, OM.OBS LEVEL3 AS BUSINESS LINE, OM.OBS LEVEL3 AS BUSINESS FUNCTION, OM.OBS LEVEL4 AS SUB PRODUCT, OM.OBS LEVEL6 AS GROUP TYPE, OM.OBS LEVEL7 AS MASTER PROJECT FROM PPM DWH.DWH INV OBS MAPPING OM WHERE OM.OBS TYPE KEY = 5000017 ORDER BY 3 ASC, 4 ASC, 5 ASC, 6 ASC, 7 ASC) MPJ WHERE INV.INVESTMENT TYPE KEY <> 'idea' AND INV.INVESTMENT KEY = INVA.INVESTMENT KEY AND INV.INVESTMENT KEY = MPJ.INVESTMENT KEY (+) AND INVA.INVESTMENT KEY = PF.INVESTMENT KEY AND INVA.ASSIGNMENT KEY = PF.ASSIGNMENT KEY AND INVA.INVESTMENT KEY = TSKPA.INVESTMENT KEY AND INVA.TEAM KEY = TM.TEAM KEY AND TM.RESOURCE KEY = RESR.RESOURCE KEY (+) AND RESR.RESOURCE KEY = PF.RESOURCE KEY (+) AND PF.RESOURCE KEY = OBS.RESOURCE KEY (+) AND TSK1.TASK KEY = TSKPA.PARENT TASK KEY (+) AND TSK1.WBS LEVEL = 1 AND TSKPA.CHILD TASK KEY = TSK2.TASK KEY (+) AND TSK2.WBS LEVEL IN (1,2) AND TSK2.WBS TYPE KEY IN ('LEVEL2','TASK') AND TSK2.TASK KEY = TSKAT.PARENT TASK KEY (+) AND TSKAT.CHILD TASK KEY = TSK3.TASK KEY (+) AND TSK3.WBS TYPE KEY = 'TASK' AND TSK3.TASK KEY = PF.TASK KEY AND PF.PERIOD KEY = CMP.PERIOD KEY AND CMP.PERIOD TYPE KEY = 'MONTHLY' AND CMP.MONTH START DATE BETWEEN '01-JAN-22' AND '01-DEC-22' GROUP BY OBS.BUSINESS LINE, OBS.PILLAR, OBS.DEPARTMENT, OBS.GROUP NAME, OBS.SECTION NAME, RESR.RESOURCE NAME, MPJ.BUSINESS FUNCTION, INV.INVESTMENT ID, INV.INVESTMENT NAME, TSK1.TASK NAME, TSK2.TASK NAME, TSK3.TASK NAME HAVING SUM(PF.ACTUAL HOURS) !