February 10, 2012 at 2:10 pm
I am relatively new to sql and need to create a procedure which will cumulate values from a table´s previous rows in the procedure, I would like to make it as simple as possible. Let me show you how a table can look like:
ID, ListID, RowID, Amount
4, 200, 1, 4
7, 200, 2, 5
9, 200, 3, 2
2, 150, 1, 10
@ID is a primary key which I am processing from an application and should always match for a ListID within I will select all corresponding ListIDs for procedure. RowID should start at 1 and increase continuously but procedure should be protected also if its not a case. For output data I need to add two columns, for RowID=1: Reserve1 starts at 0 and Reserve2=Amount, every next row follows this rule: Reserve1=Reserve2 from previous row and Reserve2 = sum of column "Amount" from current and all previous rows.
Theres how output of procedure should look like when processing parameter @ID=7 (output shoul be the same also if @ID= 4 or @ID=9):
ID, ListID, RowID, Amount, Reserve1, Reserve2
4, 200, 1, 4, 0, 4
7, 200, 2, 5, 4, 9
9, 200, 3, 2, 9, 11
I will be glad for any idea how to solve this task, thanks in advance.
February 10, 2012 at 2:39 pm
Keep in mind there is no such thing as next or previous rows. They have no order until you add an order by clause. It appears that you want to order by ID?
Regardless what you are referring to is a running total. Jeff wrote a great article about this topic http://www.sqlservercentral.com/articles/T-SQL/68467/%5B/url%5D. Post back if you run into some issues and need some help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply