Post reply

Formatting issue with percent

  • September 16, 2021 at 10:00 pm

    #3930317

    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 2 years, 7 months ago by  JeremyU.

  • September 16, 2021 at 10:03 pm

    #3930323

    ...

    CAST(p.QTY * 100.00 / s.OH AS decimal(5, 2)) AS 'Instock %'

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • September 16, 2021 at 10:17 pm

    #3930325

    @SSC Guru

    Thank you very much!!

  • September 16, 2021 at 10:20 pm

    #3930326

    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) A socialist is someone who will give you the shirt off *someone else's* back.

  • September 16, 2021 at 11:27 pm

    #3930332

    SSC Guru I see. Thank you!! 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply