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

Answer 1

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]

Answer 2

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