April 8, 2013 at 3:10 pm
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?
April 8, 2013 at 6:23 pm
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.
April 8, 2013 at 7:17 pm
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
April 9, 2013 at 3:06 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2013 at 3:25 pm
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.
FromToBehavior
numericnumericRound
numericintTruncate
numericmoneyRound
moneyintRound
moneynumericRound
floatintTruncate
floatnumericRound
floatdatetimeRound
datetimeintRound
Thank you for making me learn something new.
April 9, 2013 at 7:21 pm
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!!
April 10, 2013 at 8:15 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply