Consolidate two UPDATE statements into on

I'm working on a stored procedure to age balances. In short, here's the algo
rithm:
Payments get applied to the oldest 'bucket' first; in my example, BalanceOve
r180.
Is the value in the Payments column greater than or equal to the value in Ba
lanceOver180?
If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
If not, subtract Payments from BalanceOver180 and set Payments to 0.
Then go on to the next oldest 'bucket' (which would be Balance151To180) and
run the same two update statements.
There are a total of 7 columns against which payments need to be applied.
Here are the two statements that execute for each column:
UPDATE AgedBalances
SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
WHERE Payments >= BalanceOver180 AND Payments > 0
UPDATE AgedBalances
SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
WHERE Payments < BalanceOver180 AND Payments > 0
My question: Can these two statements be combined into one using the ABS fun
ction or the CASE construct
or something similar that I don't seem to be able to figure out?
Sample data:
BalanceOver180 Payments
Before: 200 500
After: 0 300
OR
Before: 600 500
After: 100 0
OR
Before: 400 400
After: 0 0
You get the picture.
As always, thanks in advance for all assistance.
Carl Imthurnsomething like that:
update <yor table>
set BalanceOver180 =
case
when
(Payments < BalanceOver180) then (BalanceOver180 - Payments)
when
(Payments >= BalanceOver180) then 0
end,
Payments =
case
when
(Payments < BalanceOver180) then 0
when
(Payments >= BalanceOver180) then (Payments - BalanceOver180)
end
where Payments > 0
--
Programmer
Let me know if it works for you :-))
"Carl Imthurn" wrote:

> I'm working on a stored procedure to age balances. In short, here's the al
gorithm:
> Payments get applied to the oldest 'bucket' first; in my example, BalanceO
ver180.
> Is the value in the Payments column greater than or equal to the value in
BalanceOver180?
> If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
> If not, subtract Payments from BalanceOver180 and set Payments to 0.
> Then go on to the next oldest 'bucket' (which would be Balance151To180) an
d run the same two update statements.
> There are a total of 7 columns against which payments need to be applied.
> Here are the two statements that execute for each column:
> UPDATE AgedBalances
> SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
> WHERE Payments >= BalanceOver180 AND Payments > 0
> UPDATE AgedBalances
> SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
> WHERE Payments < BalanceOver180 AND Payments > 0
> My question: Can these two statements be combined into one using the ABS f
unction or the CASE construct
> or something similar that I don't seem to be able to figure out?
> Sample data:
> BalanceOver180 Payments
> Before: 200 500
> After: 0 300
> OR
> Before: 600 500
> After: 100 0
> OR
> Before: 400 400
> After: 0 0
> You get the picture.
> As always, thanks in advance for all assistance.
> Carl Imthurn
>|||That worked - thank you very much Sergey.
Sergey Zuyev wrote:

> something like that:
> update <yor table>
> set BalanceOver180 =
> case
> when
> (Payments < BalanceOver180) then (BalanceOver180 - Payments)
> when
> (Payments >= BalanceOver180) then 0
> end,
> Payments =
> case
> when
> (Payments < BalanceOver180) then 0
> when
> (Payments >= BalanceOver180) then (Payments - BalanceOver180)
> end
> where Payments > 0