Don't Round - 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

General SQL Server Forums

New to SQL Server Programming

Don't Round

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-27 : 13:33:44

I have the following select statement:


SELECT tot_sls_amt = (select A.SumExtUnitPrice from(selecT Ord_no,sum((UNIT_PRICE*QTY_TO_SHIP)-((unit_price * qty_to_ship)*(DISCOUNT_PCT*.01)))
as SumExtUnitPrice from oeordlin_sql
group by ord_no)A
where A.ord_no = oeordhdr_sql.ord_no)
from oeordlin_sql join oeordhdr_sql on 
oeordlin_sql.ord_type = oeordhdr_sql.ord_type and oeordlin_sql.ord_no = oeordhdr_sql.ord_no
wHERE OEORDLIN_SQL.ORD_NO = '  616658'
END

This statment returns: 680.6683

Now I have the following update statement:


begin
update oeordhdr_sql
SET tot_sls_amt = (select A.SumExtUnitPrice from(selecT Ord_no,sum((UNIT_PRICE*QTY_TO_SHIP)-((unit_price * qty_to_ship)*(DISCOUNT_PCT*.01)))
as SumExtUnitPrice from oeordlin_sql
group by ord_no)A
where A.ord_no = oeordhdr_sql.ord_no)
from oeordlin_sql join oeordhdr_sql on 
oeordlin_sql.ord_type = oeordhdr_sql.ord_type and oeordlin_sql.ord_no = oeordhdr_sql.ord_no
where oeordlin_sql.line_no = 9999 and isnull(oeordlin_sql.item_desc_2,' ')<>'CHANGE' AND OEORDLIN_SQL.ITEM_NO = 'RBATE'
END

This is returning: 680.67

How do I get the 2nd statement to return the same value as the first?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:39:16

whats the datatype of tot_sls_amt in oeordhdr_sql?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-27 : 13:40:25

decimal 16,2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:42:54

quote:
Originally posted by Vack

decimal 16,2

thats why its stores only 2 decimal places. Make it decimal 18,4 and try.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-27 : 13:46:29

I can't do that. Database belongs to another software package so I don't want to change anything. If you pull the order up in a view you get the 680.66 and that is how there program calculates it. Using my script and updating the field it is rounding on me. Is there a way to truncate the value that is being updated?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:51:54

quote:
Originally posted by Vack

I can't do that. Database belongs to another software package so I don't want to change anything. If you pull the order up in a view you get the 680.66 and that is how there program calculates it. Using my script and updating the field it is rounding on me. Is there a way to truncate the value that is being updated?

you cant get 4 decimal places if you store it to the tot_sls_amt using update as storing it wil lose last two decimal places as field doesnt have precision level to store 4 decimla places. alternatively what you could do is to directly use calculation at places where you want 4 decimal places. (use first select for that)

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-27 : 13:57:02

I don't want to store all 4 decimal places.
I want to put 680.66 into the total sales amount field. Does the datatype decimal 16,2 automatically round???

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 14:06:33

Why do you care about the rounding if you only with the 2 decimal places?? wouldn't 680.67 be more closer to 680.668?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-27 : 14:08:40

In the software if the user pulls the order up in a view it shows the total as 680.66. So I need that amount to match what I'm putting into the header.

Later down the road if a user prints a report that uses the tot_sls_amt field and then they go into the software and view the order, the amounts will be off by a penny.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 14:15:50

what about this?


begin
update oeordhdr_sql
SET tot_sls_amt = (select FLOOR(A.SumExtUnitPrice*100)/100 from(selecT Ord_no,sum((UNIT_PRICE*QTY_TO_SHIP)-((unit_price * qty_to_ship)*(DISCOUNT_PCT*.01)))
as SumExtUnitPrice from oeordlin_sql
group by ord_no)A
where A.ord_no = oeordhdr_sql.ord_no)
from oeordlin_sql join oeordhdr_sql on 
oeordlin_sql.ord_type = oeordhdr_sql.ord_type and oeordlin_sql.ord_no = oeordhdr_sql.ord_no
where oeordlin_sql.line_no = 9999 and isnull(oeordlin_sql.item_desc_2,' ')<>'CHANGE' AND OEORDLIN_SQL.ITEM_NO = 'RBATE'
END