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 Tue, 26 January 2010 22:14
 Pranitha Messages: 5Registered: 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......

[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
 Michel Cadot Messages: 64809Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
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
 Frank Messages: 7880Registered: March 2000 Senior Member
Michel Cadot wrote on Wed, 27 January 2010 07:40

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
 Michel Cadot Messages: 64809Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Less than that I just copy and paste (unlike many posters I am able to copy and paste what I have on screen )

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
 Michel Cadot Messages: 64809Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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)
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
 Michel Cadot Messages: 64809Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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)
8      from t
9    )
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
 Pranitha Messages: 5Registered: 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
 Pranitha Messages: 5Registered: 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: Mon May 22 16:40:01 CDT 2017

Total time taken to generate the page: 0.12707 seconds