[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 437

# sql query issue with round up.

hi i have a query written but my calculcation on currency coming wrong. can some one help  me out.

this is my query
select priority,saleinvoiceid,invoiceidbystore,storeid1,customerid1,globalproductid,Description,quantity,unitprice,listprice,paymentname,taxitem,convert(nvarchar,invoicedate,101) invoicedate,taxid,taxrate,(taxvalue * quantity)as total
from dbo.temptable1  where convert(nvarchar,invoicedate,101)= convert(nvarchar,getdate()-2,101)
group by priority,saleinvoiceid,invoiceidbystore,storeid1,customerid1,globalproductid,Description,quantity,unitprice,listprice,paymentname,taxitem,invoicedate,taxid,taxrate,taxvalue
order by invoicedate,invoiceidbystore

total is coming wrong my total is coming \$899.10 but the total should be \$899.08

can some one help me what i am doing wrong.

0
romeiovasu
• 3
• 2
• 2
1 Solution

Commented:
Do you mean the total from multiple rows of "taxvalue * quantity", or just a single record?
If it is due to multiple records, the reason will be due to accumulated rounding errors - this sometimes cannot be avoided and requires complicated workarounds.

value = \$10.50, tax = 1%, value * tax = \$0.105 - \$0.11
2 rows, \$0.11 x 2 = \$0.22

That is already 1 cent away from \$21 + 1%, which is \$21.21
0

Commented:
Does this change the result?

Change this
(taxvalue * quantity)as total

to this
(taxvalue * quantity* 1.00000)as total

You may not need to use so many zeros after the decimal point.
0

Author Commented:
using this i am getting the same value (taxvalue * quantity* 1.00000)

the problem happening is only when the quantity is more than 1. if quantity is equal to 1 then it is coming perfectly fine.
0

Author Commented:
this is the output

1      2352      CO225IN2187      2      1755      83      CRICKET HUAWEI M860 ASCEND      1      129.99      149.99      Cash      1      11/26/2010      1      8.6      141.17
3      2352      CO225IN2187      2      1755      16      Voice \$55      1      0      0      Cash      0      11/26/2010      0      0      0
5      2352      CO225IN2187      2      1755      69      TIO New Activation      1      64.57      0      Cash      0      11/26/2010      0      0      64.57
6      2352      CO225IN2187      2      1755      82      TIO Bill Payment      1      181.41      0      Cash      0      11/26/2010      0      0      181.41
7      2352      CO225IN2187      2      1755      83      CRICKET HUAWEI M860 ASCEND      1      129.99      149.99      Cash      1      11/26/2010      1      8.6      141.17
10      2352      CO225IN2187      2      1755      133      Cricket Upgrade      1      0      0      Cash      0      11/26/2010      0      0      0
9      2352      CO225IN2187      2      1755      16      Voice \$55      1      0      0      Cash      0      11/26/2010      0      0      0
11      2352      CO225IN2187      2      1755      29      ESN Change Fee      1      15      15      Cash      0      11/26/2010      0      0      15
12      2352      CO225IN2187      2      1755      83      CRICKET HUAWEI M860 ASCEND      1      129.99      149.99      Cash      1      11/26/2010      1      8.6      141.17
14      2352      CO225IN2187      2      1755      16      Voice \$55      1      0      0      Cash      0      11/26/2010      0      0      0
16      2352      CO225IN2187      2      1755      29      ESN Change Fee      1      15      15      Cash      0      11/26/2010      0      0      15
15      2352      CO225IN2187      2      1755      133      Cricket Upgrade      1      0      0      Cash      0      11/26/2010      0      0      0
17      2352      CO225IN2187      2      1755      83      CRICKET HUAWEI M860 ASCEND      1      129.99      149.99      Cash      1      11/26/2010      1      8.6      141.17
19      2352      CO225IN2187      2      1755      16      Voice \$55      1      0      0      Cash      0      11/26/2010      0      0      0
20      2352      CO225IN2187      2      1755      133      Cricket Upgrade      1      0      0      Cash      0      11/26/2010      0      0      0
21      2352      CO225IN2187      2      1755      29      ESN Change Fee      1      15      15      Cash      0      11/26/2010      0      0      15
22      2352      CO225IN2187      2      1755      54      ANYCOM PAROS 10 BLUETOOTH      4      0      19.99      Cash      0      11/26/2010      0      0      0
23      2352      CO225IN2187      2      1755      187      SCREEN PROTECTOR- HUAWEI M860      4      3.33      9.99      Cash      1      11/26/2010      1      8.6      14.48
24      2352      CO225IN2187      2      1755      160      NON-OEM MICRO-USB CAR CHARGER      4      3.33      7.99      Cash      1      11/26/2010      1      8.6      14.48
25      2352      CO225IN2187      2      1755      191      HUAWEI M860 SILICON GEL RED      1      3.33      7.99      Cash      1      11/26/2010      1      8.6      3.62
26      2352      CO225IN2187      2      1755      193      HUAWEI M860 SILICON GEL GREEN      1      3.33      7.99      Cash      1      11/26/2010      1      8.6      3.62
27      2352      CO225IN2187      2      1755      189      HUAWEI M860 SILICON GEL CLEAR      1      3.33      7.99      Cash      1      11/26/2010      1      8.6      3.62
28      2352      CO225IN2187      2      1755      190      HUAWEI M860 SILICON GEL SMOKE      1      3.33      7.99      Cash      1      11/26/2010      1      8.6      3.62
0

Author Commented:
sorry here is the output file
Book1.xlsx
0

Commented:
Add taxvalue to the list of selected columns, it's not in the spreadsheet.

select priority,saleinvoiceid,invoiceidbystore,storeid1,customerid1,globalproductid,Description,quantity,unitprice,listprice,paymentname,taxitem,convert(nvarchar,invoicedate,101) invoicedate,taxid,taxrate,taxvalue, (taxvalue * quantity)as total
from dbo.temptable1  where convert(nvarchar,invoicedate,101)= convert(nvarchar,getdate()-2,101)
group by priority,saleinvoiceid,invoiceidbystore,storeid1,customerid1,globalproductid,Description,quantity,unitprice,listprice,paymentname,taxitem,invoicedate,taxid,taxrate,taxvalue
order by invoicedate,invoiceidbystore
0

Commented:
Hi romeiovasu,

How did adding another column into the select list correct the problem with rounding??
0

## Featured Post

• 3
• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.