Add a number to next row

Database
Fan

Add a number to next row

Hello All,

I have a table like this

Col1 Col2 Col3
========= ==== ========
8/14/2006 0 0.584942
8/14/2006 1 1.219285
8/14/2006 2 1.850245
8/14/2006 3 2.021042
8/14/2006 4 2.236441
8/14/2006 5 2.4908
8/14/2006 6 3.381762
8/14/2006 7 3.434554
8/14/2006 8 4.059867
8/14/2006 9 4.006229
8/14/2006 10 4.118866
8/14/2006 11 1.743533
8/14/2006 12 1.743533
8/14/2006 13 1.743533

from above table I have to create a table like this:
read first row from col3 and populate col4 (0.584942); add all the digits on the right side of decimal from col4 first row (0.584942) to clo3 second row (1.219285), should get 1.804227. then take 0.804227 from col4 then add to next row col3, and keep doing this till you reach (08/14/2006 -col1 13 -col2).
UPI of this table consists of Col1 and Col2.

Col1 Col2 Col3 Col4 Col5 Col6
========= ===== ======== ======== ====== ====
8/14/2006 0 0.584942 0.584942 0.584942 0
8/14/2006 1 1.219285 1.804227 0.804227 1
8/14/2006 2 1.850245 2.654472 0.654472 2
8/14/2006 3 2.021042 2.675514 0.675514 2
8/14/2006 4 2.236441 2.911955 0.911955 2
8/14/2006 5 2.4908 3.402755 0.402755 3
8/14/2006 6 3.381762 3.784517 0.784517 3
8/14/2006 7 3.434554 4.219071 0.219071 4
8/14/2006 8 4.059867 4.278938 0.278938 4
8/14/2006 9 4.006229 4.285167 0.285167 4
8/14/2006 10 4.118866 4.404033 0.404033 4
8/14/2006 11 1.743533 2.147566 0.147566 2
8/14/2006 12 1.743533 1.891099 0.891099 1
8/14/2006 13 1.743533 2.634632 0.634632 3

I would like to know how I can achieve this logic using SQL. Please find attachement for first and second tables.
Your help is appreciated.

Thanks.

2 REPLIES

Re: Add a number to next row

Hello,

you can try this SQL:
select
col2,
col3,
(sum(col3) over (order by col2 rows between unbounded preceding and 1 preceding) -
(sum(col3) over (order by col2 rows between unbounded preceding and 1 preceding) (integer) ) )
as col4tmp,
coalesce(col3 + col4tmp,col3) as col4
from you_table

Note: I got rid of col1 which is useless in your sample data but if you've got several dates in your actual data you may need to add a 'partition by col1' condtion in your window functions.

Nicolas
Fan

Re: Add a number to next row

Thanks Nicolas, that was great help.