Link to home
Start Free TrialLog in
Avatar of Senz79
Senz79Flag for India

asked on

Remove 0 from SQL result

Hello All
I am attaching the stored procedure and the output please help me to remove the 0s from the result.
senz



Mod edit: see http:#a34118133 for replacement attachment
ALTER PROCEDURE [dbo].[spTop20Server] 
	-- Add the parameters for the stored procedure here
	@TechType varchar(50),
	@Domain varchar(50),
	@Month varchar(20)
AS
BEGIN
	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 desc
END

Open in new window

Avatar of RiteshShah
RiteshShah
Flag of India image

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)
AS
BEGIN
        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 desc
END

Open in new window

Avatar of Senz79

ASKER

no there is no zero in the column
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)
AS
BEGIN
        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 desc
END

Open in new window

>>no there is no zero in the column<<

which column?


Avatar of Senz79

ASKER

Hi ritesh i need blank rather than null or 0 and the next query returns no result
Avatar of Senz79

ASKER

Just need to replace zero with blank
here you go!!! 0 with blank.

ALTER PROCEDURE [dbo].[spTop20Server] 
        -- Add the parameters for the stored procedure here
        @TechType varchar(50),
        @Domain varchar(50),
        @Month varchar(20)
AS
BEGIN
        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 desc
END

Open in new window

Avatar of Senz79

ASKER


0 is still coming , can it be replaced with blank
ASKER CERTIFIED SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Senz79

ASKER

Kindly delete the data in the question on priority.Due to some security issue
Avatar of Senz79

ASKER

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