Senz79
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
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
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
>>no there is no zero in the column<<
which column?
which column?
ASKER
Hi ritesh i need blank rather than null or 0 and the next query returns no result
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
ASKER
0 is still coming , can it be replaced with blank
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Kindly delete the data in the question on priority.Due to some security issue
ASKER
Please replace the attachment at ID:25780262 with the attached data
res.txt
res.txt
Open in new window