Using a value from a previous row to decrement a value in the current row

Question

Post reply

Using a value from a previous row to decrement a value in the current row

back_of_the_fleet

Say Hey Kid

Points: 683
More actions
July 6, 2011 at 10:52 am

#243699

I was asked by a colleague how this might be done, and I repeated an example based on code I've used myself which I'm sure I nicked originally from a forum (probably this one).
Any suggestions for a better approach as I'm sure there must be a smarter (less cumbersome) way to do this?
/* Create a dummy table and populate it */
declare @t table
(
Code nchar(3),
Balance decimal(13, 5)
)
declare
@ix decimal(13, 5),
@imax int
select
@ix = 1.00000,
@imax = 100
while @ix <= @imax
begin
insert into @t
select
right('00' + cast(cast(@ix as int) as nvarchar(3)), 3),
@ix * 456.75
select
@ix = @ix + 1
end
/* Demonstrate approach to reducing balance by the amount of the previous records balance */
select
l1RowNo = l1.RowNo,
l1Code = l1.Code,
l1Balance = l1.Balance,
l2Balance = l2.Balance,
NewBalance = l1.Balance - l2.Balance
from
(
select RowNo = row_number () over (partition by 'x' order by Code), *
from @t
) l1
left join (
select
RowNo =
row_number () over (partition by 'x' order by Code), *
from @t
) l2
on l2.RowNo = l1.RowNo - 1
Result set
l1RowNol1Codel1Balancel2BalanceNewBalance
1001456.75000NULLNULL
2002913.50000456.75000456.75000
30031370.25000913.50000456.75000
40041827.000001370.25000456.75000
50052283.750001827.00000456.75000
60062740.500002283.75000456.75000
70073197.250002740.50000456.75000
80083654.000003197.25000456.75000
90094110.750003654.00000456.75000
100104567.500004110.75000456.75000
110115024.250004567.50000456.75000
120125481.000005024.25000456.75000
130135937.750005481.00000456.75000
140146394.500005937.75000456.75000
150156851.250006394.50000456.75000
160167308.000006851.25000456.75000
170177764.750007308.00000456.75000
180188221.500007764.75000456.75000
190198678.250008221.50000456.75000
200209135.000008678.25000456.75000
210219591.750009135.00000456.75000
2202210048.500009591.75000456.75000
2302310505.2500010048.50000456.75000
2402410962.0000010505.25000456.75000
2502511418.7500010962.00000456.75000
2602611875.5000011418.75000456.75000
2702712332.2500011875.50000456.75000
2802812789.0000012332.25000456.75000
2902913245.7500012789.00000456.75000
3003013702.5000013245.75000456.75000
3103114159.2500013702.50000456.75000
3203214616.0000014159.25000456.75000
3303315072.7500014616.00000456.75000
3403415529.5000015072.75000456.75000
3503515986.2500015529.50000456.75000
3603616443.0000015986.25000456.75000
3703716899.7500016443.00000456.75000
3803817356.5000016899.75000456.75000
3903917813.2500017356.50000456.75000
4004018270.0000017813.25000456.75000
4104118726.7500018270.00000456.75000
4204219183.5000018726.75000456.75000
4304319640.2500019183.50000456.75000
4404420097.0000019640.25000456.75000
4504520553.7500020097.00000456.75000
4604621010.5000020553.75000456.75000
4704721467.2500021010.50000456.75000
4804821924.0000021467.25000456.75000
4904922380.7500021924.00000456.75000
5005022837.5000022380.75000456.75000
5105123294.2500022837.50000456.75000
5205223751.0000023294.25000456.75000
5305324207.7500023751.00000456.75000
5405424664.5000024207.75000456.75000
5505525121.2500024664.50000456.75000
5605625578.0000025121.25000456.75000
5705726034.7500025578.00000456.75000
5805826491.5000026034.75000456.75000
5905926948.2500026491.50000456.75000
6006027405.0000026948.25000456.75000
6106127861.7500027405.00000456.75000
6206228318.5000027861.75000456.75000
6306328775.2500028318.50000456.75000
6406429232.0000028775.25000456.75000
6506529688.7500029232.00000456.75000
6606630145.5000029688.75000456.75000
6706730602.2500030145.50000456.75000
6806831059.0000030602.25000456.75000
6906931515.7500031059.00000456.75000
7007031972.5000031515.75000456.75000
7107132429.2500031972.50000456.75000
7207232886.0000032429.25000456.75000
7307333342.7500032886.00000456.75000
7407433799.5000033342.75000456.75000
7507534256.2500033799.50000456.75000
7607634713.0000034256.25000456.75000
7707735169.7500034713.00000456.75000
7807835626.5000035169.75000456.75000
7907936083.2500035626.50000456.75000
8008036540.0000036083.25000456.75000
8108136996.7500036540.00000456.75000
8208237453.5000036996.75000456.75000
8308337910.2500037453.50000456.75000
8408438367.0000037910.25000456.75000
8508538823.7500038367.00000456.75000
8608639280.5000038823.75000456.75000
8708739737.2500039280.50000456.75000
8808840194.0000039737.25000456.75000
8908940650.7500040194.00000456.75000
9009041107.5000040650.75000456.75000
9109141564.2500041107.50000456.75000
9209242021.0000041564.25000456.75000
9309342477.7500042021.00000456.75000
9409442934.5000042477.75000456.75000
9509543391.2500042934.50000456.75000
9609643848.0000043391.25000456.75000
9709744304.7500043848.00000456.75000
9809844761.5000044304.75000456.75000
9909945218.2500044761.50000456.75000
10010045675.0000045218.25000456.75000
John Rogerson
BI Technical Lead
Clear Channel International

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Here is the fastest known method:

http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

back_of_the_fleet Say Hey Kid Points: 683 More actions · Answer 2

My effort looks very much like the triangular join running total - that's not good. I like the Quirky Update - I've used this approach for concatenating strings before now (client names) on small recordsets and it never occurred to me to use it this way for numerics. Great :-).

Thanks for the pointer/link (and the embedded link to the Robyn Page article too).

John Rogerson
BI Technical Lead
Clear Channel International