SELECT ACCT_NUM, CURRMNTH,CURRMNTH_CHRG, LASTMNTH_CHRG,
(CURRMNTH_CHRG - LASTMNTH_CHRG) MONTHLY_PAID_INT
FROM(
SELECT ACCT_NUM,
TO_CHAR(BUSINESS_DT,'YYYYMM') CURRMNTH,
YTD_FIN_CHRG CURRMNTH_CHRG,
LAG(YTD_FIN_CHRG) OVER
(PARTITION BY
ACCT_NUM,TO_NUMBER(TO_CHAR(BUSINESS_DT,'DD'))
ORDER BY TO_NUMBER(TO_CHAR(BUSINESS_DT,'MM'))
) LASTMNTH_CHRG
FROM DWH_BASE_MONTHLY
)
WHERE LASTMNTH_CHRG IS NOT NULL
sridharg.rao_at_gmail.com wrote:
> Hi,
>
> Here is the sample data:
>
> ACCT_NUM BUSINESS_DT YTD_FIN_CHRG
> 123401 10-OCT-04 800
> 123402 10-OCT-04 1000
> 123401 11-OCT-04 800
> 123402 11-OCT-04 1000
>
> .................................
>
> 123401 10-NOV-04 1000
> 123402 10-NOV-04 1200
>
> The computed column, MONTHLY_PAID_INTEREST is calculated by using the
> formula:
>
> MONTHLY PAID INTEREST = YTD_FIN_CHRG ON 10-NOV-04 - YTD_FIN_CHRG ON
> 10-OCT-04
>
> I need the output as:
>
> ACCT_NUM CURR_MNTH LASTMNTH_CHRG CURRMNTH_CHRG MONTHLY_PAID_INT
>
> 123401 200411 800 1000 200
> 123402 200411 1000 1200 200
>
> I am currently using a self join to compute this:
>
> SELECT
> B.ACCT_NUM,
> TO_CHAR(B.BUSINESS_DT, 'YYYYMM'),
> A.YTD_FIN_CHRG AS LASTMNTH_CHRG,
> B.YTD_FIN_CHRG AS CURRMNTH_CHRG,
> (B.YTD_FIN_CHRG_PAID - A.YTD_FIN_CHRG_PAID) AS MONTHLY_PAID_INT
> FROM
> DWH_BASE_MONTHLY A, -- Last Month
> DWH_BASE_MONTHLY B -- Current Month
> WHERE
> AND A.ACCT_NUM = B.ACCT_NUM
> AND A.BUSINESS_DT = ADD_MONTHS(B.BUSINESS_DT,-1)
>
> I want to eliminate the usage of the self-join. I want to use
> analytical functions in Oracle to do this.
> Can anyone help me on this?
>
> Thanks in advance.
>
> Cheers!
> Sridhar
Received on Fri Jan 21 2005 - 12:58:29 CST
