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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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