How about

WITHcte1 AS (SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id) AS 'total',max(b.budget) as budget

FROM #TestData a

INNER JOIN #TestBudget b on (a.account = b.account)

GROUP BY a.id, a.account, a.deposit

),

cte2 AS (SELECT*,budget-total AS RemainingBudget

FROM cte1

),

cte3 AS (SELECT *,

CASE

WHEN RemainingBudget>0 THEN Deposit

WHEN Deposit+RemainingBudget>0 THEN Deposit+RemainingBudget

ELSE 0

END AS InBudget

FROM cte2

)

SELECT*,

id,

account,

deposit,

InBudgetAS [Renewal],

deposit-InBudgetAS [Unbudgetted]

FROM cte3

Which gives results:

4Bills40400

5Bills50545

6Bills60060

7Party70700

8Party80080

1Vacation10100

2Vacation20155

3Vacation30030