Yes definitely these solutions would use SSIS to move data. I currently have option one working via SSIS. The merge is done using plain t-sql though.

Sweet thanks Jeff! 🙂

Thanks for commenting Jeff. I actually got my result from the The ‘Subscription List’ SQL Problem but since the part I used is essentially the same as your post I...

ColdCoffee was on the money the "quirky update" method worked a treat. Thanks ColdCoffee!

Here is the complete solution

declare @calc integer

declare @data table(

thedate datetime,

...

The real world calculation is Cumulative Performance which I can post the formula on Monday. The only complexity for me lies on the fact that I need the previous rows...

Unfortunately performance is not great. If I bump up the test case to 3K rows its 1 second for cursor based approach and 15 seconds for recursive cte.

Thanks "SSC Eights!" but this wont for larger datasets due the heavy recursion.

forgot to state this is not a running total issue, this is contrived example of much more complex financial calculation.