TempTable has columns RunningTotal and ClientCount, we also have @RunningTotal variable declared and set to 0.
Can someone please explain what does this line do ?
UPDATE Temptable
SET @RunningTotal = RunningTotal = @RunningTotal + ClientCount
Never seen this construct before, but it seems to work like this.
It fills column RunningTotal with a cumulative total of ClientCount.
Say we start with a table with just ClientCount filled in:
CREATE TABLE dbo.Temptable (ClientCount int, RunningTotal int)
INSERT INTO Temptable (ClientCount) VALUES (5), (4), (6), (2)
SELECT * FROM Temptable
ClientCount RunningTotal
----------- ------------
5 NULL
4 NULL
6 NULL
2 NULL
And then run the update statement:
DECLARE @RunningTotal int = 0
UPDATE Temptable SET @RunningTotal = RunningTotal = @RunningTotal + ClientCount
SELECT * FROM Temptable
ClientCount RunningTotal
----------- ------------
5 5
4 9
6 15
2 17
As you can see, each value of RunningTotal is the sum of all ClientCount values from the current and any preceding records.
The downside is, you have no control in which order the records are processed. Which makes me wonder whether this is a recommended approach in a production environment.
Please check here for a deeper discussion: Calculate a Running Total in SQL Server
