Koen Verbeeck (8/1/2012)
ChrisM@Work (8/1/2012)
polkadot (7/31/2012)
... I need an alias for the column the WHERE STATEMENT generates...The WHERE clause doesn't generate an output column. Ever.
There are four well-known ways to calculate running totals in SS2k8:
1. Quirky update - fastest
2. Recursive CTE
3. Cursor
4. Triangular Join - usually slowest but depends upon partitioning.
The code you've posted is an attempt at coding the TJ method. If the number of elements in each GROUP BY partition is small compared to the entire data set then it may be worth pursuing, however most folks would recommend the QU for speed or the rCTE for ease of coding.
The quirky update method relies on undocumented behaviour if I'm not mistaken, and is thus unreliable. For very large datasets, the cursor method is actually the preferred method.
Luckily window functions are introduced in SQL 2012 🙂
There will be opinions about this 🙂
variable = column = expression is documented in both UPDATE and MERGE but MS give no clue about usage. Then again, MS are lousy at providing examples and showing how a feature should be used.
If the running total is to be persisted, I'll usually go for the QU. If it's for output (reporting) I'll always go for a rCTE from a suitably indexed #temp table, because it's likely to be quicker than a cursor or a TJ.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden