A table displaying the required results would be helpful.
Try this:
;WITH RunningTotal AS (
SELECT
a.id, a.account, a.deposit, b.Budget,
[Total] = SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id)
FROM #TestData a
INNER JOIN #TestBudget b
ON a.account = b.account
)
SELECT
id, account, deposit, Budget, total,
x.RemainingBudget,
x.InBudget,
Renewal = InBudget,
x.Unbudgeted
FROM RunningTotal
CROSS APPLY (
SELECT
[RemainingBudget] = CASE WHEN Total<=Budget THEN Budget-Total ELSE 0 END,
[Unbudgeted] = CASE WHEN Total>=Budget THEN Total-Budget ELSE 0 END,
[InBudget] = CASE
WHEN Total<=Budget THEN deposit
WHEN Total-Deposit > Budget THEN 0
ELSE Deposit+Budget-Total END
) x
ORDER BY id;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden