SQL QUERY TO ROLLOVER VALUES FROM PRIOR YEAR TO CURRENT YEAR 1

[rohithrn]

HI ALL,

Here is the scenario:

In a table I have three columns year, price and expenses respectively. The values in it are as follows:

YEAR PRICE EXPENSES

1/1/1999 4502 5681
1/1/2000 9644 157
1/1/2001 1036 16401
1/1/2002 1897 6443
1/1/2003 8275 2390
1/1/2004 1405 1163
1/1/2005 69125 6790
1/1/2006 6225 6435
1/1/2007 21045 3043
1/1/2008 32125 3653

From this table I need to get the output which should be as follows:

YEAR PRICE ADJ SUBTOTAL EXPENSES CORR

1/1/1999 4502 0 4502 5681 -1179
1/1/2000 9644 -1179 8465 157 8308
1/1/2001 1036 8308 9344 16401 -7057
1/1/2002 1897 -7057 -5160 6443 -11603
1/1/2003 8275 -11603 -3328 2390 -5718
1/1/2004 1405 -5718 -4313 1163 -5476
1/1/2005 69125 -5476 63649 6790 56859
1/1/2006 6225 56859 63084 6435 56649
1/1/2007 21045 56649 77694 3043 74651
1/1/2008 32125 74651 106776 3653 103123

The logic is:

Initially (for year 1999) ADJ is assumed to be 0. SUBTOTAL is sum of PRICE(PRICE is from table) and ADJ. CORR is SUBTOTAL-EXPENSES(EXPENSES is from table).

CORR for year 1999 is to be followed to next year (2000) ADJ. SUBTOTAL will be sum of PRICE and ADJ (for example for yr 2000 subtotal = PRICE of yr 2000 + CORR of 1999 i.e subtotal = 9644 + (-1179)). Then EXPENSES for year 2000 is subtracted from this SUBTOTAL of yr 2000 to get the CORR for year 2000. CORR of year 2000 shud carry forward to 2001 as ADJ. This continues...

Can anyone please help me in writting the SQL query to execute this functionality in oracle?


Thanks in Advance.

 

[cmmrfrds]

The lead or lag Analytical function should work for the problem. Look these up on the web, there are plenty of examples.

 

[rohithrn]

Hi,

Thanks for the suggestion. I have learned the 'LEAD' and 'LAG' analytical functions and these helped me in solving my issue.

Thank You