Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rounding up


Rounding up

Author
Message
dr.mannhattan
dr.mannhattan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 4
Hello Everyone!

I have this issue where i use a store procedure to create a report:

ALTER Procedure [dbo].[A_RepChequesxCheque]


@FechaInicio T_DATETIME,
@FechaTermino T_DATETIME

AS

Begin
select ant.CheckId, Cheque,
Fecha,
Departamento,
ISNULL (Cajero,0),
SL2.Importe,
SL2.Propina,
ISNULL (SL2.FormaDePago, 'Cancelado') as FormaDePago,
ISNULL(descuento.DescImp, 0) As DescImp,
1.16 * ISNULL(descuento.DescImp, 0) As DescImpIVA,
SL2.Importe + 1.16 * ISNULL(descuento.DescImp, 0) + SL2.Propina As TotalBruto,
ISNULL ((SL2.Importe + SL2.Propina) / 1.16,0) As MontoNeto,
ISNULL ((SL2.Importe - SL2.Propina - SL2.ScAmt) -((SL2.Importe - SL2.Propina - SL2.ScAmt)/ 1.16),0) As ImpIVA,
(SL2.Importe + SL2.Propina) As MontoTotal,
Empleado,
NoMesa,
Personas
from (Select XCH.CheckId, XCH.CheckNo As Cheque,
XCH.TransactionDate As Fecha,
KD.Name As Departamento,
(KE.FirstName + KE.LastName ) As Cajero,
KEB.Name As Empleado,
XCT.Name As NoMesa,
XCH.Covers As Personas

From K_Employee KE,
X_CheckHeader XCH,
K_Department KD,
K_EmpBadge KEB,
X_CheckTable XCT

Where XCH.IsCurrent = 1
and XCH.Active = 0
And XCH.ServerId = KEB.ServerId
And KE.EmpId = XCH.SettledId
And XCH.SetDept = KD.DeptNo
And XCH.TransactionDate Between @FechaInicio And @FechaTermino
And XCT.CheckId = XCH.CheckId

Group by XCH.CheckId, XCH.CheckNo, XCH.TransactionDate, KD.Name,
KE.FirstName, KE.LastName,KEB.Name,XCT.Name,XCH.Covers) ant
LEFT OUTER JOIN (Select CheckId, Sum(PromoAmt) DescImp
from X_CheckItem, X_CheckPromo
where X_CheckItem.ItemId = X_CheckPromo.ItemId
group by CheckId) descuento
ON ant.CheckId = descuento.CheckId

LEFT OUTER JOIN (Select XCH.CheckId,
ISNULL (Sum(XCP.PayAmt),0) As Importe,
(ISNULL (Sum(XCP.TipAmt),0)*-1) As Propina,
ISNULL (Sum(Servicio.ScAmt),0) as ScAmt,
ISNULL (KP.Name,'Cancelado') As FormaDePago
From X_CheckHeader XCH
left join (select XCH.CheckId, Sum(isnull(XCSVC.ScAmt,0)) As ScAmt
from X_CheckHeader XCH,
X_CheckItem XCI,
X_CheckSrvcChrg XCSVC
where XCH.IsCurrent = 1
And XCH.Active = 0
And (XCH.TransactionDate Between @FechaInicio And @FechaTermino)
And XCI.CheckId = XCH.CheckId
And XCSVC.ItemId = XCI.ItemId
GROUP BY XCH.CheckId
)Servicio on XCH.CheckId = Servicio.CheckId,
X_CheckPay XCP,
K_Payment KP
Where XCH.IsCurrent = 1
and XCH.Active = 0
And XCH.CheckId = XCP.CheckId
And XCH.TransactionDate Between @FechaInicio And @FechaTermino
And KP.PaymentID = XCP.PaymentID
Group by XCH.CheckId,KP.Name, ScAmt) SL2
ON ant.CheckId = SL2.CheckId

Order by Cheque Asc


End


GO


It returns almost every field correct except for the IMPIVA where it returns 2 decimals rounded.

ISNULL ((SL2.Importe - SL2.Propina - SL2.ScAmt) -((SL2.Importe - SL2.Propina - SL2.ScAmt)/ 1.16),0) As ImpIVA,

If the math comes 45.856 Returns 45.86

is there a way to prevent this?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8590 Visits: 18174
It shouldn't be rounding if you're not explicitly rounding it.
Can you post DDL for the table that contain those fields?
If you're using ROUND(), use the third argument (with a non-zero value) to indicate that SQL Server must truncate and not round.

EDIT: After examining your query, I found there's a big mess. You should avoid the old types of joins and keep only the new ones to improve readability. You could avoid at least one subquery and maybe even two.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Mansfield
Mansfield
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 394
This is probably because of the data types of your numerical columns. Please post DDL statements for all the tables referenced in the query.

Also take a look at the Data Type Precedence article:
http://msdn.microsoft.com/en-us/library/ms190309%28v=sql.100%29.aspx
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3957 Visits: 6697
SQL will automatically cast the result to the receiving data type; cast will automatically round.

To prevent rounding, you can explicitly use the ROUND function yourself (kinda ironic):


ROUND(ISNULL ((SL2.Importe - SL2.Propina - SL2.ScAmt) -((SL2.Importe - SL2.Propina - SL2.ScAmt)/ 1.16),0), 2, 1) As ImpIVA, --the final ", 1" on the ROUND function "tells" it to truncate, not round

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8590 Visits: 18174
ScottPletcher (4/9/2013)
SQL will automatically cast the result to the receiving data type; cast will automatically round.


I've always thought it truncated the decimals instead of rounding them (which also happens). Afters reading BOL, I found this under CAST and CONVERT


When you convert data types that differ in decimal places,
sometimes the result value is truncated
and at other times it is rounded. The following table shows the behavior.
From To Behavior
numeric numeric Round
numeric int Truncate
numeric money Round
money int Round
money numeric Round
float int Truncate
float numeric Round
float datetime Round
datetime int Round


Thank you for making me learn something new.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
dr.mannhattan
dr.mannhattan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 4
ROUND(ISNULL ((SL2.Importe - SL2.Propina - SL2.ScAmt) -((SL2.Importe - SL2.Propina - SL2.ScAmt)/ 1.16),0), 2, 1) As ImpIVA, --the final ", 1" on the ROUND function "tells" it to truncate, not round
[/quote]


Thank you so much Scott that works perfectly!!
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3957 Visits: 6697
dr.mannhattan (4/9/2013)



ROUND(ISNULL ((SL2.Importe - SL2.Propina - SL2.ScAmt) -((SL2.Importe - SL2.Propina - SL2.ScAmt)/ 1.16),0), 2, 1) As ImpIVA, --the final ", 1" on the ROUND function "tells" it to truncate, not round



Thank you so much Scott that works perfectly!![/quote]


Great ... I'm glad it helped you!

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search