ta.bu.shi.da.yu (10/11/2010)
If I read this correctly, you just implemented the LAG(...) Over(...) analytic function.
Cool, I didn't even know this existed. But it looks like it's only available in Oracle databases. Microsoft, please bring this over!
However, I'm interested... if you do this on a large dataset, what does the execution plan look like?
Let me try to get my execution plan and I'll get back to you. I do remember that our initial process ran painfully slow and we had just 45K rows. It ran for something like 45 seconds (almost 1 sec/1K rows) using a scalar function. This updated process now takes less than one second to complete.