?
Solved

Format to Percentage in SQL Server Query

Posted on 2016-10-04
8
Medium Priority
?
272 Views
Last Modified: 2016-10-04
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]

Open in new window

0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41828001
you can try to cast as an integer;

cast(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 integer) + '%' AS [% CMP]

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41828009
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]

Open in new window

Be aware that I edited this post!
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41828036
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 1000 total points
ID: 41828047
you have an extra )

try this:
CAST(CAST(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 INT) AS VARCHAR) + '%' 
AS [% CMP]

Open in new window

0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 41828052
I mentioned that I edited my post because it had errors. I will post it again here:
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]

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41828054
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41828074
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.
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 41828121
Thank you both for all your help. If I could give you both BEST solution, I would but it only lets you choose one.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question