I have developed a query, and in the results for the first three columns I get NULL
. How can I replace it with 0
?
Select c.rundate,
sum(case when c.runstatus="Succeeded" then 1 end) as Succeeded,
sum(case when c.runstatus="Failed" then 1 end) as Failed,
sum(case when c.runstatus="Cancelled" then 1 end) as Cancelled,
count(*) as Totalrun from
( Select a.name,case when b.run_status=0 Then 'Failed' when b.run_status=1 Then 'Succeeded'
when b.run_status=2 Then 'Retry' Else 'Cancelled' End as Runstatus,
---cast(run_date as datetime)
cast(substring(convert(varchar(8),run_date),1,4)+"https://stackoverflow.com/"+substring(convert(varchar(8),run_date),5,2)+"https://stackoverflow.com/" +substring(convert(varchar(8),run_date),7,2) as Datetime) as RunDate
from msdb.dbo.sysjobs as a(nolock) inner join msdb.dbo.sysjobhistory as b(nolock)
on a.job_id=b.job_id
where a.name="AI"
and b.step_id=0) as c
group by
c.rundate