ROUND probelm on QA
-
Hello comunity
I have the following SELECT :
Select ft.nome as 'Cliente',ft.nmdoc,cast(ft.fno as char(10)) as'NrDoc',conta as 'Contaiva',cast(round(decmes,0) as varchar) as 'Cp.DecPer.',decricao as 'Descrição',fi.u_coddpiva as 'Cod.do IVA',cast(fi.u_taxiva as char(2)) as 'TaxaIVA',
sum(case when fi.ivaincl=1 then fi.etiliquido/(1+fi.iva/100) else fi.etiliquido end) as 'B.Incid',
sum(case when fi.ivaincl=1 then round((fi.etiliquido/(1+fi.iva/100))* (1+fi.iva/100)-fi.etiliquido,3) else round(fi.etiliquido*(1+fi.iva/100)-fi.etiliquido,3) end) as 'IVA',
sum(case when fi.ivaincl=1 then round((fi.etiliquido/(1+fi.iva/100))* (1+fi.iva/100),3) else round(fi.etiliquido*(1+fi.iva/100),3) end) as 'Total'
from fi (nolock) inner join ft (nolock) on (ft.ftstamp=fi.ftstamp and ft.fno=fi.fno)
inner join u_ivnew on fi.u_coddpiva=convert(char(3),u_ivnew.cod)
where fi.composto=0 and ft.fdata between '20070101' and '20070131' and fi.fno in (10008,10028) and
(ft.tipodoc=1 or ft.tipodoc=2 or ft.tipodoc=3) and fi.etiliquido<>0
group by ft.nome,ft.nmdoc,cast(ft.fno as char(10)),u_coddpiva,cast(fi.u_taxiva as char(2)) ,conta,cast(round(decmes,0) as varchar),decricao
order by ft.nmdoc,cast(ft.fno as char(10)) ,cast(round(decmes,0) as varchar) asc
Note : the value of FI.IVA is always : 21
The field FI.ETILIQUIDO is always calculate in my invoice line by line with 2 decimal and is :
Quantity * unitprice
for the first document nº 10008 ,Q.A. show me :
B.Incid = 4132.50000000000000000
IVA = 867.82500000000000
Total = 5000.32500000000000
For the second document nº 10028:
B.Incid = 12550.72000000000000000
IVA = 2635.65100000000000
Total = 15186.37100000000000
But if I change the ROUND for 2 decimal on the columns ‘IVA’ and ‘TOTAL’, the result is :
for the first document nº 10008 (it´s OK)
B.Incid = 4132.50000000000000000
IVA = 867.83000000000000
Total = 5000.33000000000000
For the second document nº 10028: (it´s incorrect)
B.Incid = 12550.72000000000000000
IVA = 2635.67000000000000
Total = 15186.39000000000000
I don’t understand why ??
Could someone give me an ideia
Many thanks
Luis Santos
-
The answer is because you are doing the rounding first and then taking the sum.
By the way... the first letter of SQL stands for "Structured"... you might want to apply some structure to your code to make it a little easier to read... just a thought, though
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply