Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update Query

Author

Topic

das
Starting Member

8 Posts

Posted - 2002-07-15 : 10:22:24

I have table similar to the following that is created from a download.

Create Table tblTransactions
( upcharge int,
accountcode int,
std_cost numeric(10,5),
amount numeric(18,0),
ysnVarianceRec bit,
ysnDebitCredit bit)

There can be 3 records to create a balanced entry. The variance record is the difference between the std_cost of a debit and a credit.
If you take the amount * std_cost of the debit versus the amount*std_Cost of the credit it should be zero. Sometimes due to rounding the entry is off +-1.00. I need to update the nonvariance record by a +-1.00 to bring the entry in balance.

Ex. Upcharge AccountCode stdCost Amount Debit/Credit Varinace
123 123 .800 80 0 0
123 123 .600 60 1 0
123 123 .200 20 1 1

The above is a balance entry but lets say the following happened .

Ex. Upcharge AccountCode stdCost Amount Debit/Credit Varinace
123 123 .800 80 0 0
123 123 .600 -61 1 0
123 123 .200 -20 1 1

I would need to add 1.00 to debit amount of 80 to bring to 81 to balance the entry. I can write the select query with group by and get the differences

Select sum(amount)
from tblTransaction
group by upcharge, AccountCode
but have trouble write the update to go through this table and adjust entries.

Thanks in Advance
Derek

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-15 : 12:07:23

das,

You could do something like this (assuming the CREDIT side is always correct).

UPDATE tblTransactions
Set tblTransactions.amount = tblTransactions.amount - b.amount
FROM tblTransactions,
(Select upcharge, AccountCode, sum(amount) amount
from tblTransactions
group by upcharge, AccountCode) b
WHERE tblTransactions.upcharge = b.upcharge
AND tblTransactions.AccountCode = b.AccountCode
AND tblTransactions.ysnDebitCredit = 0

Let me know if this is not what you are looking for.

Jeremy

* This also assumes that you always have one DEBIT amount, no matter how many credit amounts. If this is not the case (i.e. 2 DEBITs and 2 CREDITs), then this statement will not work.*

Edited by - joldham on 07/15/2002 12:09:19

das
Starting Member

8 Posts

Posted - 2002-07-15 : 12:47:15

We can't always assume credit side is correct. It maybe the debit side is correct. You can assume that there would be only 1 to many whether it be the debit or the credit. 1 debit 2 or more credits or 1 credit 2 or more debits.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-15 : 14:38:00

Setting up sample data

Create Table tblTransactions
(upcharge int,
accountcode int,
std_cost numeric(10,5),
amount numeric(18,0),
ysnVarianceRec bit,
ysnDebitCredit bit)

INSERT INTO tblTransactions
SELECT 123, 123, .800, 80, 0, 0
UNION ALL SELECT 123, 123, .600, -61, 0, 1
UNION ALL SELECT 123, 123, .200, -20, 1, 1
UNION ALL SELECT 124, 123, .800, 80, 0, 0
UNION ALL SELECT 124, 123, .600, -62, 0, 1
UNION ALL SELECT 124, 123, .200, -20, 1, 1
UNION ALL SELECT 125, 123, .800, 80, 0, 1
UNION ALL SELECT 125, 123, .600, -62, 0, 0
UNION ALL SELECT 125, 123, .200, -20, 1, 0

Now Update either the debit or credit that has only one entry to equal the remaining part of the transaction that has more than one entry.

UPDATE tblTransactions
Set tblTransactions.amount = tblTransactions.amount - bb.amount
FROM tblTransactions,
(Select a.upcharge, a.AccountCode, a.ysnDebitCredit, b.amount
FROM (Select upcharge, AccountCode, ysnDebitCredit
FROM tblTransactions
GROUP BY upcharge, AccountCode, ysnDebitCredit Having Count(*) = 1) a INNER JOIN
(Select upcharge, AccountCode, sum(amount) amount
from tblTransactions
group by upcharge, AccountCode) b
ON a.upcharge = b.upcharge
AND a.AccountCode = b.AccountCode) bb
WHERE tblTransactions.upcharge = bb.upcharge
AND tblTransactions.AccountCode = bb.AccountCode
AND tblTransactions.ysnDebitCredit = bb.ysnDebitCredit

Final results from above data.

123 123 .80000 81 0 0
123 123 .60000 -61 0 1
123 123 .20000 -20 1 1

124 123 .80000 82 0 0
124 123 .60000 -62 0 1
124 123 .20000 -20 1 1

125 123 .80000 82 0 1
125 123 .60000 -62 0 0
125 123 .20000 -20 1 0

If this doesn't work correctly, let me know what isn't right so I can modify. Another suggestion I have is to add a bit field within the tblTransactions table to indicate whether or not this update has ran. By doing this, you could modify this query to only update transactions that have not been previously updated.

Jeremy

das
Starting Member

8 Posts

Posted - 2002-07-16 : 07:08:08

Thanks for your all of help Jeremy. This appears to be working fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources