ALTER PROCEDURE [dbo].[spTop20Server] -- Add the parameters for the stored procedure here @TechType varchar(50), @Domain varchar(50), @Month varchar(20)ASBEGIN SET NOCOUNT ON; select top 20 * from ( SELECT elementname, COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, COUNT(*) AS grandtotal FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and sourcedomainname like '%' + @Domain + '%' and DATENAME(MM,snhinsertedat) = @Month GROUP BY elementname ) as t order by grandtotal descEND

are you sure you have alter your SP? anyway try this one.

ALTER PROCEDURE [dbo].[spTop20Server] -- Add the parameters for the stored procedure here @TechType varchar(50), @Domain varchar(50), @Month varchar(20)ASBEGIN SET NOCOUNT ON; select top 20 elementname, case when sev1=0 then '' else cast(sev1 as varchar) end as sev1, case when sev2=0 then '' else cast(sev2 as varchar) end as sev2, case when sev3=0 then '' else cast(sev3 as varchar) end as sev3, case when sev4=0 then '' else cast(sev4 as varchar) end as sev4, case when sev5=0 then '' else cast(sev5 as varchar) end as sev5 from ( SELECT elementname, COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, COUNT(*) AS grandtotal FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and sourcedomainname like '%' + @Domain + '%' and DATENAME(MM,snhinsertedat) = @Month GROUP BY elementname ) as t order by grandtotal descEND

don't you want to look at 0 in sev1,2 etc. fields?

ALTER PROCEDURE [dbo].[spTop20Server] -- Add the parameters for the stored procedure here @TechType varchar(50), @Domain varchar(50), @Month varchar(20)ASBEGIN SET NOCOUNT ON; select top 20 elementname, case when sev1=0 then NULL else sev1 end as sev1, case when sev2=0 then NULL else sev2 end as sev2, case when sev3=0 then NULL else sev3 end as sev3, case when sev4=0 then NULL else sev4 end as sev4, case when sev5=0 then NULL else sev5 end as sev5 from ( SELECT elementname, COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, COUNT(*) AS grandtotal FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and sourcedomainname like '%' + @Domain + '%' and DATENAME(MM,snhinsertedat) = @Month GROUP BY elementname ) as t order by grandtotal descEND

if you don't want to see the row if any of them have 0 in sev fields, than use this one

ALTER PROCEDURE [dbo].[spTop20Server] -- Add the parameters for the stored procedure here @TechType varchar(50), @Domain varchar(50), @Month varchar(20)ASBEGIN SET NOCOUNT ON; select top 20 * from ( SELECT elementname, COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, COUNT(*) AS grandtotal FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and sourcedomainname like '%' + @Domain + '%' and DATENAME(MM,snhinsertedat) = @Month GROUP BY elementname ) as t where sev1<>0 and sev2<>0 and sev3<>0 and sev4<>0 and sev5<>0 order by grandtotal descEND

ALTER PROCEDURE [dbo].[spTop20Server] -- Add the parameters for the stored procedure here @TechType varchar(50), @Domain varchar(50), @Month varchar(20)ASBEGIN SET NOCOUNT ON; select top 20 elementname, case when sev1=0 then '' else sev1 end as sev1, case when sev2=0 then '' else sev2 end as sev2, case when sev3=0 then '' else sev3 end as sev3, case when sev4=0 then '' else sev4 end as sev4, case when sev5=0 then '' else sev5 end as sev5 from ( SELECT elementname, COUNT(CASE WHEN severity = '1' THEN '1' END) AS sev1, COUNT(CASE WHEN severity = '2' THEN '1' END) AS sev2, COUNT(CASE WHEN severity = '3' THEN '1' END) AS sev3, COUNT(CASE WHEN severity = '4' THEN '1' END) AS sev4, COUNT(CASE WHEN severity = '5' THEN '1' END) AS sev5, COUNT(*) AS grandtotal FROM itm_alerts where classdisplayname like '%' + @TechType + '%' and sourcedomainname like '%' + @Domain + '%' and DATENAME(MM,snhinsertedat) = @Month GROUP BY elementname ) as t order by grandtotal descEND

Open in new window