hi; thx for your input. It’s checking for another JOB. So this JOB checks for another JOB and determine if it failed a number of times and send email from this JOB.
SELECT count(*) as ct, run_status,[name]
FROM msdb.dbo.sysJobHistory h (NOLOCK)
INNER JOIN msdb.dbo.sysJobs j (NOLOCK)
AND j.name=‘MyJOB’ —this job run every 10 mins
AND CAST(MSDB.dbo.agent_datetime(run_date, run_time) AS DATETIME) > DATEADD(minute, -60, getdate()) —60 minute span…if notice it failed over 5 times I get the email from this job. Not an email from the job it’s checking that failed.
group by run_status,[name]
having count(j.name)=5 –number of times it failed.