Lawrence Salvucci
asked on
Format to Percentage in SQL Server Query
I am trying to format my result to a percentage with 0 decimals. This is what I have for the formatting/SQL syntax. Can anyone help me reset this to work correctly? Right now when the results are displayed it shows this for an example: 100.00000000%
CASE WHEN (jodrtg.fnqty_comp / jodrtg.foperqty) * 100 > 0 AND jodbom.fqty_iss = '0' AND joitem.fprodcl = '01' THEN ((Round(jodrtg.fnqty_comp, 0) / Round(jodrtg.foperqty, 0)) * Round(100, 0)) * - 1 ELSE (Round(jodrtg.fnqty_comp, 1) / Round(jodrtg.foperqty, 0)) * Round(100, 0) END * 1) + '%' AS [% CMP]
Use this:
cast(cast(round(
CASE
WHEN (jodrtg.fnqty_comp / jodrtg.foperqty) * 100 > 0 AND jodbom.fqty_iss = '0' AND joitem.fprodcl = '01'
THEN
-(jodrtg.fnqty_comp / jodrtg.foperqty *100.0)
ELSE
jodrtg.fnqty_comp /jodrtg.foperqty * 100.0
END
,0) as int) as varchar(5)) + '%' AS [% CMP]
Be aware that I edited this post!
ASKER
Neither of those seem to be working. I am getting a syntax errors when I try to execute my query.
Error in list of function arguments: 'AS' not recognized.
Error in list of function arguments: ',' not recognized.
Unable to parse query text.
Error in list of function arguments: 'AS' not recognized.
Error in list of function arguments: ',' not recognized.
Unable to parse query text.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Eric's post will not round the values correctly. You have to use ROUND before casting to int.
select 100.0/6, cast(100.0/6 as int), cast(round(100.0/6, 0) as int)
--------------------------------------- ----------- -----------
16.666666 16 17
Actually he did use round but twice inside the case branches, which could be multiple. I used it only once wrapping the whole case statement, which I think is the better way.
ASKER
Thank you both for all your help. If I could give you both BEST solution, I would but it only lets you choose one.
Open in new window