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

connection timeout

Hi there
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
Loane
Hi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>

connection timeout

Hi there
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
LoaneHi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>