Current Month - Previous Month computation without using Self-Join

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 - 03:21:14 CST