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

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

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

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Please replace the attachment at ID:25780262 with the attached data res.txt

0

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for freeEdge Out The Competitionfor your dream job with proven skills and certifications.Get started todayStand Outas the employee with proven skills.Start learning today for freeMove Your Career Forwardwith certification training in the latest technologies.Start your trial today

Open in new window