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

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

Fred Webb

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

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

Fred Webb

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

Fred Webb

ASKER

Najam,

Same Error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

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

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.

Fred Webb

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.

Fred Webb

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.

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

Fred Webb

ASKER

No problem Scott, I should have caught that myself, yeah we do have some percentage swings.