Partition Over/Partition By Example

This article provides an example of a query that uses Partition By Functions.

Detailed Information

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.

The Query performs the following:

  • Returns employee earnings by chartfield, 
  • Compares that to the total earnings for the employee by Accounting Period or Fiscal Year
  • Returns a percentage 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 
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), 3)
 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), 3)
 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)

Environment

  • PeopleSoft Human Resources (HRPRD)
Print Article