Hi Mohit

Nice posting!

One of my DBA brought your post in my attention when he was working on finding list of jobs current status is not successful.

I happened to notice little change requirement in your query:

You should replace last part using max(instance_id) that will help user to find latest status even when job ran multiple times in a day. To be precise:

syntax part:

instance_id = (select max(hi.instance_id) from msdb.dbo.sysjobhistory hi where h.job_id = hi.job_id)

Just thought and suggestion!