AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of skull52
skull52
Flag for United States of America asked on

Desplay Results as a percentage

I have Script that outputs a percentage value in the results, Purchase_Percent_2014 and YTD_Purchase_Percent_2015 currently they go out 6 decimal places I would like it to display just 1 decimal place with the % symbol for example 22.5% currently it is displaying 22.500000


SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014, VPP.YTD_Purchase_Ammount_2015, ISNULL(VPP.Purchase_Percent_2014, 0) AS Purchase_Percent_2014, 
ISNULL(VPP.YTD_Purchase_Percent_2015, 0) AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year]) 
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015, 
(SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) 
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) 
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Avatar of undefined
Last Comment
skull52
8/22/2022 - Mon
Najam Uddin
Crazy query :)

Round

& 

CONTACT
lcohan
SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014, VPP.YTD_Purchase_Ammount_2015,
      --ISNULL(VPP.Purchase_Percent_2014, 0) AS Purchase_Percent_2014,
      CONVERT(VARCHAR(50),ISNULL(VPP.Purchase_Percent_2014, 0)+' %' AS Purchase_Percent_2014,
      --ISNULL(VPP.YTD_Purchase_Percent_2015, 0) AS YTD_Purchase_Percent_2015
      CONVERT(VARCHAR(50),ISNULL(VPP.YTD_Purchase_Percent_2015, 0)+' %' AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
(SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52
ASKER
Najam,
Yeah... I know it is a bit out there, but it works. I should have said I am on MSSQL 2008R2 so I can't use CONCAT
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Najam Uddin
SELECT VPP.Vendor_ID, VPP.Vendor_Name, ROUND(VPP.Purchase_Ammount_2013,2), ROUND(VPP.Purchase_Ammount_2014,2), ROUND(VPP.YTD_Purchase_Ammount_2015,2),
      CONVERT(VARCHAR(50),ROUND(ISNULL(VPP.Purchase_Percent_2014, 0),2)+' %' AS Purchase_Percent_2014,
      CONVERT(VARCHAR(50),ROUND(ISNULL(VPP.YTD_Purchase_Percent_2015, 0),2)+' %' AS YTD_Purchase_Percent_2015
FROM          
 (SELECT  Vendor_ID, Vendor_Name
, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013
, SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014
, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015
, (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014
, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            
SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52
ASKER
lcohan,
I get the following error
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
lcohan
sorry missed the ROUND - added with 2 decimals instead of one but you can easily change it to 1 only:


SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014, VPP.YTD_Purchase_Ammount_2015,
      --ISNULL(VPP.Purchase_Percent_2014, 0) AS Purchase_Percent_2014,
      CONVERT(VARCHAR(50),ISNULL(cast(round(VPP.YTD_Purchase_Percent_2014,2) as numeric(36,2)), 0))+' %' AS Purchase_Percent_2014,
      --ISNULL(VPP.YTD_Purchase_Percent_2015, 0) AS YTD_Purchase_Percent_2015
      CONVERT(VARCHAR(50),ISNULL(cast(round(VPP.YTD_Purchase_Percent_2015,2) as numeric(36,2)), 0))+' %' AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
(SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
skull52
ASKER
Najam,

Same Error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
ASKER CERTIFIED SOLUTION
lcohan
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
skull52
ASKER
lcohan,
Perfect, Thanks
Scott Pletcher
SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014,
              VPP.YTD_Purchase_Ammount_2015,
              ISNULL(CAST(VPP.Purchase_Percent_2014 AS varchar(6)) + '%', '0') AS Purchase_Percent_2014,
              ISNULL(CAST(VPP.YTD_Purchase_Percent_2015 AS varchar(6)) + '%', '0') AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
CAST((SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(5, 2)) AS Purchase_Percent_2014, CAST((SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(5, 2)) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
lcohan
phew...and as said if you really want only one decimal in the percentage(I left 2 as I believe this is "regular" way to display percentages) you can change the

round(VPP.YTD_Purchase_Percent_2014,2) as numeric(36,2)

like

round(VPP.YTD_Purchase_Percent_2014,2) as numeric(36,1)

to get only one decimal place.
skull52
ASKER
Yeah... I know this was a twisted script, but I was given little time to do it.  That's why I had a hard time figuring out how to do the conversions, I knew i could use round in there but wasn't sure of the syntax and the concatenation of the % symbol, thanks again.
skull52
ASKER
Scott,
Thanks for your input I tried you code and it generated the following Error  
Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher
Sorry, would need to increase the ranges, didn't expect you to have that type of % gains :-):

SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014,
              VPP.YTD_Purchase_Ammount_2015,
              ISNULL(CAST(VPP.Purchase_Percent_2014 AS varchar(20)) + '%', '0') AS Purchase_Percent_2014,
              ISNULL(CAST(VPP.YTD_Purchase_Percent_2015 AS varchar(20)) + '%', '0') AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
CAST((SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(17, 2)) AS Purchase_Percent_2014, CAST((SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(17, 2)) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52
ASKER
No problem Scott, I should have caught that myself, yeah we do have some percentage swings.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent