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_2015FROM (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_2015FROM SSG_Vendor_Yearly_TotalsGROUP BY Vendor_ID, Vendor_Name, Class_IDHAVING (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN(SELECT CUSTNMBR, CUSTCLASFROM RM00101WHERE (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBRWHERE (RM.CUSTNMBR IS NULL)ORDER BY VPP.Vendor_ID
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
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
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
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.
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.
Round
&
CONTACT