>Kevin,
>
>Just an example how to do this in SQL 2005 without CTE's


Wow, thanks, that actually performs better than anything I'd seen prior to the new windowing features in SQL 2012. My first inclination is to use CROSS APPLY though there's additional cost to that.


On the CTEs - in absence of recursion, they offer no functional benefit - they are syntax sugar for derived table subqueries (though some people prefer sugar!).


Thanks, great answers. Wrap-up tonight, have to get back to work (or my power-nap, whichever I decide) :)