Solved: SQL Query Assistance: What jobs run on Sunday from 7am - 12pm

jeff.hamway01 · ‎09-15-2016

Hello Tidal Admins,

I need to make a SQL query that can give me a list of jobs that run on a particular day (Sunday) from 7am-12pm. Our environment does not have a maintenance window. So every time there is patching or upgrades, it causes more work and risk of Tidal job failures. By moving jobs out of the Sunday 7am-12pm timeframe, patching can be completed without interrupting the schedule.

Im not the best at SQL scripting and can not get my script to work. any help would be great. below is what I have so far. I get error ' the multi-part identifier jobmst.jobmst_name cant be found. Im using Tidal 6.0.3 with a SQL table for the Admeral DB.

SELECT dbo.jobdtl.jobdtl_id, dbo.jobmst.jobmst_prntname,dbo.jobmst.jobmst_name

FROM dbo.jobdtl INNER JOIN

dbo.jobmst ON dbo.jobdtl.jobdtl_id = dbo.jobmst.jobdtl_id

WHERE (dbo.jobdtl.jobdtl_fromdt > '09/14/2016 06:00:00 am') AND (dbo.jobdtl.jobdtl_fromdt < '09/11/2016 012:00:00 pm')

GROUP BY dbo.jobdtl.jobdtl_id, dbo.jobmst.jobmst_prntname, dbo.jobmst.jobmst_name

Derrick Au · ‎09-16-2016

Hi Jeff

The time window from the jobdtl will only return jobs that have an Early Start Time and Late Start Time windows, so there may be some jobs defined that are not time bound but have dependencies on other jobs, variables, etc.

Below are 2 SQL queries against the jobrun table and jobmst:

--To list out jobs that ran last Sunday between 7:00am and 12:00pm

SELECT jobmst.jobmst_prntname, jobmst.jobmst_name, jobrun.jobrun_time FROM jobrun
JOIN jobmst on jobrun.jobmst_id = jobmst.jobmst_id
WHERE jobrun.jobrun_time >= '9/11/2016 7:00 AM' and jobrun.jobrun_time <= '9/11/2016 12:00 PM'
ORDER BY jobrun.jobrun_time

--To list out jobs that are scheduled to run this coming Sunday between 7:00am and 12:00pm

SELECT jobmst.jobmst_prntname, jobmst.jobmst_name, jobrun.jobrun_esttime FROM jobrun
JOIN jobmst on jobrun.jobmst_id = jobmst.jobmst_id
WHERE jobrun.jobrun_esttime >= '9/18/2016 7:00 AM' and jobrun.jobrun_esttime <= '9/18/2016 12:00 PM'
ORDER BY jobrun.jobrun_esttime

BR,

Derrick Au

View solution in original post

Derrick Au · ‎09-16-2016

Hi Jeff

The time window from the jobdtl will only return jobs that have an Early Start Time and Late Start Time windows, so there may be some jobs defined that are not time bound but have dependencies on other jobs, variables, etc.

Below are 2 SQL queries against the jobrun table and jobmst:

--To list out jobs that ran last Sunday between 7:00am and 12:00pm

SELECT jobmst.jobmst_prntname, jobmst.jobmst_name, jobrun.jobrun_time FROM jobrun
JOIN jobmst on jobrun.jobmst_id = jobmst.jobmst_id
WHERE jobrun.jobrun_time >= '9/11/2016 7:00 AM' and jobrun.jobrun_time <= '9/11/2016 12:00 PM'
ORDER BY jobrun.jobrun_time

--To list out jobs that are scheduled to run this coming Sunday between 7:00am and 12:00pm

SELECT jobmst.jobmst_prntname, jobmst.jobmst_name, jobrun.jobrun_esttime FROM jobrun
JOIN jobmst on jobrun.jobmst_id = jobmst.jobmst_id
WHERE jobrun.jobrun_esttime >= '9/18/2016 7:00 AM' and jobrun.jobrun_esttime <= '9/18/2016 12:00 PM'
ORDER BY jobrun.jobrun_esttime

BR,

Derrick Au

jeff.hamway01 · ‎09-16-2016

Derrick. You are the man. I really appreciate all your feedback and assistance you give to us in this forum.

I'll be adding this query to my list.

Derrick Au · ‎09-16-2016

Hi Jeff

Always a pleasure to share knowledge, and help whenever and wherever I can

Cheers

Derrick Au