Formatting issue with percent

  • 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.

    • This topic was modified 1 month, 1 week ago by  JeremyU.
  • ...

    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."

  • @SSC Guru

    Thank you very much!!

  • 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."

  • 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