SELECT from 2 tables question- Please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SELECT from 2 tables question- Please help

Author

Topic

boing
Starting Member

8 Posts

Posted - 2006-09-02 : 09:34:04

Hi,

I'm sure this is pretty simple, but I'm stumped as how to do it myself. Basically what I want to do is get some information on SQL jobs, using tables in MSDB. Here's the non-working statement:

select name, count(*) from sysjobs sj where sj.job_id in (select job_id from sysjobhistory where run_status = '0')

This obviously won't work. What I want to do is get the name from sysjobs, and then beside it have the amount of times that job failed (run_status = 0 means that job failed). In sysjobshistory, theres a row for each time a job was run, and it sets run_status accordingly.
The job_id in sysjobs matches the job_id in sysjobshistory.

How can I construct this statement?
It's easy to do in two seperate selects, but I'm using a stored procedure, so it must all be in one recordset. My SQL (obviously) isn't great, but I suspect a solution is easy enough???

Any help would be HUGELY appreciated.

Thanks,
Cormac Redmond

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-02 : 10:07:02

Something like this...

SELECT SJ.[NAME], COUNT(*)
FROM SYSJOBHISTORY SJH 
JOIN SYSJOBS SJ ON SJH.JOB_ID = SJ.JOB_ID 
WHERE SJH.RUN_STATUS = '0'
GROUP BY SJ.[NAME]

Harsh Athalye
India.
"Nothing is Impossible"

boing
Starting Member

8 Posts

Posted - 2006-09-02 : 13:02:05

Thanks for the reply, but that's not what I was looking for.
What I want is a list of the jobs, and beside them the number of times they failed, IF they failed.

BUT, if none failed, I still get the name of the job, and either 0 or NULL in the second field.

So basically, my end result is a record set with all the job names, and a number of times they failed, if they did.

Kristen
Test

22859 Posts

Posted - 2006-09-03 : 11:30:48

This maybe?


SELECT SJ.[NAME], 
       [FailedCount] = SUM(CASE WHEN SJH.RUN_STATUS = '0' THEN 1 ELSE 0 END)
FROM SYSJOBHISTORY SJH 
JOIN SYSJOBS SJ ON SJH.JOB_ID = SJ.JOB_ID 
WHERE SJH.RUN_STATUS = '0'
GROUP BY SJ.[NAME]
ORDER BY BY SJ.[NAME]

Kristen

boing
Starting Member

8 Posts

Posted - 2006-09-05 : 07:30:04

I found a way since, but that works nicely too, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources