Solved

Format to Percentage in SQL Server Query

Posted on 2016-10-04
8
31 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 69

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 26

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
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 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 26

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 26

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculated columns 13 61
email about the whoisactive result 7 23
Updating a table from a temp table 4 28
Error running stored procedure 11 15
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now