September 16, 2021 at 10:00 pm
I have an issue that may be an easy fix for someone, but is not for me.
I need to calculate Instock%. For this I need to take p.QTY (Forecast Demand) / s.OH (What is on hand) and get a percentage.
For example p.QTY (Forecast) was 2 and Inventory On Hand is 100 now so I need to get 2% as a result.
However, when I just divide p.QTY / s.OH AS 'Instock %' I simply get a 0.
SELECT s.OH AS 'Inventory On Hand', P.QTY AS 'Forecast Demand (sales order)', p.QTY / s.OH AS 'Instock %' FROM SCPOMGR.SKU s JOIN SCPOMGR.PLANARRIV P ON S.ITEM=P.ITEM AND S.LOC=P.DEST
Does someone know how I can format or change the way I divide so that I get a %.
Thank you very much in advance!!
ps. I use SQL Server Management Studio.
September 16, 2021 at 10:03 pm
...
CAST(p.QTY * 100.00 / s.OH AS decimal(5, 2)) AS 'Instock %'
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2021 at 10:17 pm
@SSC Guru
Thank you very much!!
September 16, 2021 at 10:20 pm
You're welcome!
Btw, you got 0 in the original calc because 2/100 is 0.02. But, since the values were integer, SQL makes the result integer and thus 0 only (the .02 is lost).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2021 at 11:27 pm
SSC Guru I see. Thank you!!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy