asked
Calculate to show exact result neither rounded up or down
I need this formula to display the exact result when divided: SET Order_More_Copies = ((t.HoldCount/ @nNumberOfHolds) - t.ItemCount) The stored procedure identifies bibliographic titles that may need more items ordered to meet the Holds to copy ratio of 5:1 One of the rows lists 100 items with 694 holds. A calculation (divide holds/@nRatio) determines how many more items need to be ordered to meet that ratio. In this case the result is 138.80 which I then subtract from 100 items to get 38.80. The staff want that decimal displayed so they can make the determination to order the additional copy. When I use SET Order_More_Copies =CONVERT(DECIMAL(5,1),1.00 * t.HoldCount / @nNumberOfHolds) - t.ItemCount the result is 39 When I use SET Order_More_Copies = ((t.HoldCount/ @nNumberOfHolds) - t.ItemCount) the result is 38 Using the actual numbers gives me the correct value select on line 121: CONVERT(DECIMAL(5,2),1.00 *694/ 5) -10038.80 I need to show the 38.80 so at the end-user’s discretion, they can order that “extra” copy should they wish. I have searched for examples on how to state in the formula that I need the exact computation, neither rounded up or down. What might I use. Thank you. Jon Lellelid [1]: /storage/temp/2234-expressing-calculation-result-as-decimal.txt
I'm betting that @nNumberOfHolds is an integer? Which will cause the division operation to produce an integer result. Try changing that variable to a decimal type, or cast it as a decimal in the calculation.
I set the variable to decimal and used cast in the calculation and still get an integer. Would also consider rounding the number up. How would I do that? [link text][1] [1]: /storage/temp/2269-set-statement-and-calculation.txt
Aha! Thanks Grant for the decimal hint. I got the formula to show 65.8 for example. I had DECLARE @nNumberOfHolds decimal (5,1) ; In the table variable, I set the output column to decimal: Order_More_Copies decimal (5,1) NULL Then used this formula: SET Order_More_Copies =(ROUND(t.HoldCount, 5,1)/@nNumberOfHolds) - t.ItemCount
15 People are following this question.
