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 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP