declare @lastrun varchar(20)
declare @diff varchar(20)
set @lastrun=(select MIN(run_date)from msdb.dbo.sysjobhistory)
set @diff=(select DATEDIFF(DAY, @lastrun, Getdate()))
print @diff
--print @lastrun
if(@diff>0)
begin
--print @lastrun
SELECT j.[name],
case j.enabled when 0 then 'Disabled'
when 1 then 'enabled'
End as [enabled],
CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS [Status]
into #tempjobhistory
FROM (SELECT a.job_id,MAX(a.instance_id) As [instance_id]
FROM msdb.dbo.sysjobhistory a
WHERE a.step_id = 0
GROUP BY a.job_id) b
INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id=b.instance_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id
select * from #tempjobhistory where DATEDIFF(DAY, [LastRun], Getdate()) >=90
drop table #tempjobhistory
end
else
print 'Table having History less than 90 days'
