dm_unseen-571460 (11/10/2009)
I'm missig the CTE/Recursive approaches to solving these issues. While usually not single pass the beat triangular joinsFor Running total see: http://shahamishm.blogspot.com/2009/04/how-to-do-running-sum-using-cte-sql.html
For finding previous row ordered by key_nr and trans_date(clustered key):
WITH trans_cte(key_nr, trans_date,field1,field2,row_order)
AS(
SELECT
key_nr
,trans_date
,field1
,field2
,ROW_NUMBER()OVER(PARTITIONBY key_nr ORDERBY trans_date ASC) row_order
FROM [dbo].[trans_stage_table]
)
SELECT BASE.key_nr
,BASE.trans_date
,BASE.field1
,BASE.field2
FROM trans_cte BASE
LEFTJOIN trans_cte LAG ON BASE.key_nr = LAG.key_nr
AND BASE.row_order = LAG.row_order+1
and BASE.field1 = LAG.field1
and BASE.field2 = LAG.field2
where LAG.key_nr isnull
Thank you for the link to your article where a running total is resolved by a recurrsive CTE. That's one of the methods I left out due to the shear length (22 pages in Word) of this article. Please take the following constructively... it's not meant as a slam, just an observation. What I believe you'll find is that it's performance is similar to or worse than a cursor. But, before I stick my foot too far into my mouth, I need to test it against the million row test table I created for this article. I'll be back (hopefully tonight) to let you know how that went. Of course, I'll post the code I tested with so that you and others can also run the same test.
Thank you for your interest in solving the running total problem.
--Jeff Moden
Change is inevitable... Change for the better is not.