question

jonlellelid avatar image
jonlellelid 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
decimalcalculationscast-convert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image
Mister Magoo answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jonlellelid avatar image
jonlellelid answered
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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jonlellelid avatar image
jonlellelid answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.