Rounding up

  • 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?

  • 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
  • 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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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.

    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
  • 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!!

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply