I'm trying to get a list of jobs and their most recently completed step from the MSDB database. For a single job, this is pretty straight forward, something like:
SELECT TOP 1 j.job_id, j.name, h.step_name, h.run_date, h.run_time, h.step_id FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id WHERE j.name = 'my favorite job' ORDER BY h.run_date DESC, h.run_time DESC, h.step_id DESC
But how can I get a list of ALL jobs and their most recently completed step?
Note this is SQL 2000, so I can't use PARTITION OVER. I also can't do a SELECT MAX() because its not a single RunDateTime field, I have to sort descending by two fields (OrderDay, OrderTime).
Edit: Probably not possible, but I'd love to be able to something like:
SELECT j.job_id, ... FROM msdb.dbo.sysjobs j INNER JOIN (SELECT TOP 1 step_name, run_date, run_time, step_id FROM msdb.dbo.sysjobhistory WHERE job_id = j.job_id ORDER BY run_date desc, run_time desc, step_id desc) as H
or something, but it doesn't seem to like syntax like that...