DARTS : Aggregate and Analytic Functions- Partition Over/Partition By etc.

UMS_DTS_PARTITION_BY_EXAMPLE is a Public Query in HRPRD that uses Partition over/Partition By Analytic Functions to return total earnings by Emplid, Empl Record.

REPORT-709 - Aggregate Earnings by Employee, Account, Account % of Total Earnings In Progress

The Query performs the following:

Returns employee earnings by chartfield,
Compares that to total earnings for employee by Accounting Period or Fiscal Year
Returns a percent of total earnings for each chartfield

Analytic functions are performed after the Group By and Having clauses are executed.
This means that Analytic Aggregations must be executed inside the case statement expressions for the PS Query Tool to Group the criteria correctly.

UMS_DTS_PARTITION_BY_EXAMPLE

/*+UMS_DTS_PARTITION_BY_EXAMPLE 02_21_24*/
SELECT
 A.FISCAL_YEAR,
-- A.PAY_END_DT,
 /*+TOTAL_EARNINGS*/ ROUND(SUM(CASE WHEN SUM(A.EARNINGS) > 0 
 THEN SUM(A.EARNINGS)END) OVER(PARTITION BY A.EMPLID, A.EMPL_RCD),2)
 TOTAL_EARNINGS, 
 /*+CHARTFIELD_EARNINGS*/ ROUND(SUM(A.EARNINGS), 2)
 CHARTFIELD_EARNINGS, 
 /*+CHARTFIELD_PERCENT*/ ROUND(SUM(A.EARNINGS) / SUM(CASE WHEN SUM(A.EARNINGS) > 0 
 THEN SUM(A.EARNINGS) END) OVER(PARTITION BY A.EMPLID, A.EMPL_RCD), 2)
 CHARTFIELD_PERCENT,
 A.EMPLID,
 A.EMPL_RCD,
 A.DEPTID_CF,
 A.FUND_CODE,
 A.ACCOUNT,
 A.PROGRAM_CODE,
 A.PROJECT_ID,
 A.PRODUCT,
 A.CLASS_FLD,
 A.OPERATING_UNIT
 
FROM SYSADM.PS_UM_PAYEARNS_VW A
 WHERE (A.FISCAL_YEAR = 2024  
 -- AND A.ACCOUNTING_PERIOD = 7 
 AND A.EMPLID = '0022923')
 GROUP BY
           A.FISCAL_YEAR,
      --  A.PAY_END_DT,
           A.EMPLID,
           A.EMPL_RCD,
          A.DEPTID_CF,
          A.FUND_CODE,
          A.ACCOUNT,
          A.PROGRAM_CODE,
          A.PROJECT_ID,
          A.PRODUCT,
          A.CLASS_FLD,
          A.OPERATING_UNIT
HAVING( /*+CHARTFIELD_EARNINGS*/ ROUND(SUM(A.EARNINGS), 0) > 0)