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

• 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

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