Tuesday, June 15, 2021

Getting Cumulative Sum (Running Total) Using Analytical Functions in oralce

SELECT
    DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL,ENAME) CUMDEPTTOT,
    SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTAL,
    SUM(SAL) OVER (ORDER BY DEPTNO, SAL) CUMTOT,
    SUM(SAL) OVER () TOTSAL
FROM
    SCOTT.EMP
ORDER BY
    DEPTNO

  SELECT COMPANY,
           ORDER_NO,
           CUSTOMER_ID,
           INVOICE_ID,
           SUM(OPEN_AMOUNT*(0.2)/365*DDD) AS DELAY_CHARGE_CAL
    FROM 
    (
    SELECT CCPP.COMPANY,
           CCPP.ORDER_NO,
           IDENTITY AS CUSTOMER_ID,
           CCPP.INVOICE_ID,
           PP.INSTALLMENT_ID,
           to_char(to_date(DUE_DATE,'DD-MON-RR'),'DD-MON-RR') DUE_DATE, 
           to_number(to_char(last_day(DUE_DATE),'dd')) DD,
           SUM(to_number(to_char(last_day(DUE_DATE),'dd'))) OVER (ORDER BY DUE_DATE) DDD,
           C_Credit_Account_Hist_API.Get_Open_Amount(CCPP.COMPANY, CCPP.INVOICE_ID, INSTALLMENT_ID) OPEN_AMOUNT
           C_Credit_Account_Hist_API.Get_Open_Amount(CCPP.COMPANY, CCPP.INVOICE_ID, INSTALLMENT_ID)*.2/365*SUM(to_number(to_char(last_day(DUE_DATE),'dd'))) OVER (ORDER BY DUE_DATE) DC
    FROM C_CREDIT_PAYMENT_PLAN CCPP, 
            PAYMENT_PLAN PP 
    WHERE CCPP.COMPANY=PP.COMPANY
      AND CCPP.INVOICE_ID=PP.INVOICE_ID
      AND CCPP.ORDER_NO=ORDER_NO_
      AND PP.INSTALLMENT_ID<>'1'
      AND to_date(DUE_DATE,'DD-MON-RR')<=REPORT_DATE_
      ORDER BY to_date(DUE_DATE,'DD-MON-RR')
    )
    GROUP BY COMPANY,
             ORDER_NO,
             CUSTOMER_ID,
             INVOICE_ID

No comments:

Post a Comment

To generate a PDF using JavaScript in Oracle APEX from a collection

  To generate a PDF using JavaScript in Oracle APEX from a collection, you can follow these steps: 1. Create a button or link on your APEX p...