calculate balance in row by row
-
hi all
parden me
i cannot speak english well
i want calculate balance in evry row and set this field
for sample:
id pay recive balance
1 50000 0 50000
2 0 23000 37000
3 66000 0 103000
4 0 43000 60000
.
.
i want solve this problem to way very rapidly and best
-
What you are describing is running totals. This article http://www.sqlservercentral.com/articles/T-SQL/68467/[/url] by Jeff Moden has a great of accomplishing this.
_______________________________________________________________
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/ -
One very fast method is detailed here:
http://pavelpawlowski.wordpress.com/2010/09/30/sql-server-and-fastest-running-totals-using-clr/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Just for the record, SQL Server 2012 (only) gives us a neater T-SQL solution (though not as fast):
DECLARE @test-2 TABLE
(
Id integer PRIMARY KEY,
Amount smallmoney NOT NULL,
Balance smallmoney NULL
)
INSERT @test-2
(Id, Amount)
VALUES
(1, +50000),
(2, -23000),
(3, +66000),
(4, -43000);
UPDATE ToUpdate
SET Balance = CalcBalance
FROM
(
SELECT
t.Id,
t.Balance,
CalcBalance =
SUM(t.Amount) OVER (
ORDER BY t.Id
ROWS BETWEEN
UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM @test-2 AS t
) AS ToUpdate
SELECT * FROM @test-2 AS t;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
thank you very much SQL Kiwi for help you
mer30
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply