We help IT Professionals succeed at work.

sql syntax , split amounts , avoid rounding errors

267 Views
Last Modified: 2020-04-13
i am having the following tables in a inventory control application

items transaction table
trans_id                  integer       (/* primary key */)
trans_item_code    varchar(10)
trans_qty                decimal
trans_val                decimal

some of the items (cosmetic, medicines f.e)  have lot numbers. for this purpose another transaction file exist for lots

lots tranasction file
================
lot_id                    integer   (/* primary key */)
trans_id               integer   (/* foreign key  between two tables*/)
lot_nr                   nvarchar(8)
lot_qty                 decimal

i want to create a select that will return the following columns

lot_id,
trans_id
lot_nr
lot_qty
lot_val

lot val should be equal to trans_val * lot_qty / trans_qty. (trans_qty is always <> 0).

i want the select statement to be written in such a way so the sum of l lot_val columns for a specific trans_id to be exacty equal to trans_val.

i am using sql server 2000
Comment
Watch Question

Author

Commented:
trans_val field have the format of decimal(18,2). lot_val must have the same format
I think you have anything you need in your initial post. Do the select with exactly your calculation and wrap it in a  ROUND(<your_expression>,2) and you will get it rounded to decimal(18,2)
CERTIFIED EXPERT

Commented:
SELECT a.lot_id, a.trans_id, a.lot_nr, a.lot_qty, (b.trans_val * a.lot_qty) / b.trans_qty AS lot_val
FROM LotsTransTableName AS a
INNER JOIN ItemsTransactionTableName AS b ON LotsTransTableName.trans_id = LotsTransTableName.trans_id

Author

Commented:
i will give an example about how rounding errors may happen if no extra care is taken

trans_qty = 5678.99
trans_val = 12987.27

lot qty (suppose that we have 4 records in transaction lot table)
   789,79
   856,88
   256,87
3775,45

lot_val should be equal to
1806,17
1959,60
587,44
8634,07

sum of all lot_val = 12987,28 a little bit different from trans_val
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
lot_val should contain 2 digits only since its represents money value. in our old cobol programs we did something different. when we were in the last record of each lot collection we calculated the sum of the computed lot_val, subtract the original trans_val and then add the difference to the lot_val of the last record. with this way it was impossible to have rounding problems. can we do something like it with sql language?
CERTIFIED EXPERT

Commented:
SELECT a.lot_id, a.trans_id, a.lot_nr, a.lot_qty,
CAST((CAST((CAST(b.trans_val AS real) * CAST(a.lot_qty AS real)) / CAST(b.trans_qty AS real) AS decimal) * 100)AS int) / 100 AS lot_val
FROM LotsTransTableName AS a
INNER JOIN ItemsTransactionTableName AS b ON LotsTransTableName.trans_id = LotsTransTableName.trans_id
----
This way you force the lot_val to have just two decimal digits, and not more.
But the problem is in trans_val, which is wrongly rounded, not in lot_val.
I think I am missing something.... if u need to keep the trans_val, can't u select that value directly?

Author

Commented:
the reason  is that we take this report by two ways

1) group by item_code only (in this case we use the trans_val)
2) group by item_code and  lot_nr.

we want both reports to give the same results
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Did you try Post# 25793591? Whats the problem with that post?

Author

Commented:
the basic concept of this question is this

we have a number with 2 digits (in our exampe trans_val) and want it to split it to n other numbers in our case (lot_vals). how can we be sure that the sum of these number is equal to the original one ?
Sharath SData Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
you may used the data i mentioned yesterday. if you see the results (i give them too) the sum of them is different from the original amount.
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
In #25794181, you mentioned the lot_val values. I am looking for data in items and lots tables

Author

Commented:
we changed our design a litte bit concerning the tables
to avoid rounding errors
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.