none
Calculation rounding to two decimal places on it's own. RRS feed

  • Question

  • I'm attempting to calculate two fields to get a Remainder decimal: (Qty/per) - FLOOR(Qty/per) . What should be .41666667 is automatically turned to .42. Both of the Qty & Per fields data types are Numeric(15,5). What's confusing is when I do Qty/per I get what I want in the number of decimal places 4.416666.

    Select
    *,  Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     (Qty/per) - FLOOR(Qty/per) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces]
    From Impact

    Friday, November 9, 2018 3:38 PM

Answers

  • Hi,

    Try to convert it to Float, like this :

     (CAST(Qty AS FLOAT)/CAST(per AS FLOAT)) - FLOOR((CAST(Qty AS FLOAT)/CAST(per AS FLOAT))) as Remainder,


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by David9501 Friday, November 9, 2018 4:00 PM
    Friday, November 9, 2018 3:47 PM

All replies

  • Hi,

    Try to convert it to Float, like this :

     (CAST(Qty AS FLOAT)/CAST(per AS FLOAT)) - FLOOR((CAST(Qty AS FLOAT)/CAST(per AS FLOAT))) as Remainder,


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by David9501 Friday, November 9, 2018 4:00 PM
    Friday, November 9, 2018 3:47 PM
  • did you try after explicitly casting?

    Select
    *,  Qty/per as [# of Cartons],
     FLOOR(Qty/per) as [Full Cartons],
     CAST((Qty/per) AS numeric(15,5)) - CAST(FLOOR(Qty/per) as Numeric(15,5)) as Remainder,
     ((Qty/per) - FLOOR(Qty/per)) * (Per) as [In Pieces]
    From Impact


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, November 9, 2018 3:48 PM