Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR
SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440739] Tue, 26 January 2010 22:14 Go to next message
Pranitha
Messages: 5
Registered: January 2010
Junior Member
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 for this in Oracle?


Thanks in Advance.

[Mod-Edit: Frank added code-tags]

[Updated on: Wed, 27 January 2010 00:41] by Moderator

Report message to a moderator

Re: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440785 is a reply to message #440739] Wed, 27 January 2010 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This in unreadable.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel
Re: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440786 is a reply to message #440785] Wed, 27 January 2010 00:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 27 January 2010 07:40
This in unreadable.

Not anymore. Took me 5 seconds to add code tags. How long did it take you to write your post?
Re: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440790 is a reply to message #440786] Wed, 27 January 2010 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Less than that I just copy and paste (unlike many posters I am able to copy and paste what I have on screen Smile )

Regards
Michel
Re: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440828 is a reply to message #440739] Wed, 27 January 2010 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
drop table t;
create table t (year integer, price integer, expenses integer);
insert into t values (1999,	4502,	5681);
insert into t values (2000,	9644,	157);
insert into t values (2001,	1036,	16401);
insert into t values (2002,	1897,	6443);
insert into t values (2003,	8275,	2390);
insert into t values (2004,	1405,	1163);
insert into t values (2005,	69125,	6790);
insert into t values (2006,	6225,	6435);
insert into t values (2007,	21045,	3043);
insert into t values (2008,	32125,	3653);
commit;

SQL> select * from t order by year;
      YEAR      PRICE   EXPENSES
---------- ---------- ----------
      1999       4502       5681
      2000       9644        157
      2001       1036      16401
      2002       1897       6443
      2003       8275       2390
      2004       1405       1163
      2005      69125       6790
      2006       6225       6435
      2007      21045       3043
      2008      32125       3653

10 rows selected.

SQL> select year, price, 
  2         nvl(sum(price-expenses) over
  3              (order by year
  4               rows between unbounded preceding and 1 preceding)
  5            ,0) adj,
  6         price
  7         + nvl(sum(price-expenses) over
  8                (order by year
  9                 rows between unbounded preceding and 1 preceding)
 10              ,0) subtotal,
 11         expenses,
 12         sum(price-expenses) over (order by year) corr
 13  from t
 14  order by year
 15  /
      YEAR      PRICE        ADJ   SUBTOTAL   EXPENSES       CORR
---------- ---------- ---------- ---------- ---------- ----------
      1999       4502          0       4502       5681      -1179
      2000       9644      -1179       8465        157       8308
      2001       1036       8308       9344      16401      -7057
      2002       1897      -7057      -5160       6443     -11603
      2003       8275     -11603      -3328       2390      -5718
      2004       1405      -5718      -4313       1163      -5476
      2005      69125      -5476      63649       6790      56859
      2006       6225      56859      63084       6435      56649
      2007      21045      56649      77694       3043      74651
      2008      32125      74651     106776       3653     103123

10 rows selected.

Regards
Michel
Re: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440829 is a reply to message #440828] Wed, 27 January 2010 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe it is clearer like this:
SQL> with 
  2    data as (
  3      select year, price, expenses,
  4             nvl(sum(price-expenses) over
  5                   (order by year
  6                    rows between unbounded preceding and 1 preceding)
  7                ,0) adj
  8      from t
  9    )
 10  select year, price, adj, price+adj subtotal, expenses, price+adj-expenses corr
 11  from data
 12  order by year
 13  /
      YEAR      PRICE        ADJ   SUBTOTAL   EXPENSES       CORR
---------- ---------- ---------- ---------- ---------- ----------
      1999       4502          0       4502       5681      -1179
      2000       9644      -1179       8465        157       8308
      2001       1036       8308       9344      16401      -7057
      2002       1897      -7057      -5160       6443     -11603
      2003       8275     -11603      -3328       2390      -5718
      2004       1405      -5718      -4313       1163      -5476
      2005      69125      -5476      63649       6790      56859
      2006       6225      56859      63084       6435      56649
      2007      21045      56649      77694       3043      74651
      2008      32125      74651     106776       3653     103123

10 rows selected.

Regards
Michel

[Updated on: Wed, 27 January 2010 04:43]

Report message to a moderator

Re: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440871 is a reply to message #440829] Wed, 27 January 2010 11:33 Go to previous messageGo to next message
Pranitha
Messages: 5
Registered: January 2010
Junior Member
Hi Michel,

Thank you. The query you replied me is working perfectly.

As this is the first time i am using a site to post my question I have missed adding the code-tags. Sorry for troubling you by not posting it properly. From next time I wil make sure i post properly.

From your query I came to know few more functions in oracle. Thanks a lot.
Re: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR [message #440872 is a reply to message #440786] Wed, 27 January 2010 11:40 Go to previous message
Pranitha
Messages: 5
Registered: January 2010
Junior Member
Thanks a lot for adding the code-tags for my post.
Previous Topic: Error while executing the MERGE statement (merged)
Next Topic: Query to fetch all the months in a year using connect by (merged 3)
Goto Forum:
  


Current Time: Fri Dec 09 03:34:56 CST 2016

Total time taken to generate the page: 0.09708 seconds