Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rounding up Expand / Collapse
Author
Message
Posted Monday, April 08, 2013 3:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:20 PM
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?
Post #1440034
Posted Monday, April 08, 2013 6:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 2,763, Visits: 5,915
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1440085
Posted Monday, April 08, 2013 7:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:36 PM
Points: 364, Visits: 381
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
Post #1440095
Posted Tuesday, April 09, 2013 3:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:53 PM
Points: 1,746, Visits: 2,553
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)
I'm not fat, I'm gravity challenged.
Post #1440580
Posted Tuesday, April 09, 2013 3:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 2,763, Visits: 5,915
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1440587
Posted Tuesday, April 09, 2013 7:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 5:20 PM
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!!
Post #1440625
Posted Wednesday, April 10, 2013 8:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:53 PM
Points: 1,746, Visits: 2,553
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)
I'm not fat, I'm gravity challenged.
Post #1440831
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse