Hi Warren,
The data from job activity can be extracted from the "jobrun" table. And use JOIN statements with jobmst, jobdtl, nodmst etc to build a more comprehensive report.
SELECT
dbo.jobrun.jobrun_proddt as [PROD DATE],
dbo.jobmst.jobmst_prntname as [PARENT GROUP],
dbo.jobmst.jobmst_name as [JOB NAME],
dbo.jobrun.jobrun_time as [START TIME],
dbo.jobrun.jobrun_duration as [DURATION (s)],
CASE dbo.jobrun.jobrun_status
WHEN '1' THEN 'JOB_STATUS_WAIT'
WHEN '3' THEN 'JOB_STATUS_HOLD'
WHEN '51' THEN 'JOB_STATUS_ACTIVE'
WHEN '52' THEN 'JOB_STATUS_STOP'
WHEN '53' THEN 'JOB_STATUS_DEFERRED'
WHEN '66' THEN 'JOB_STATUS_ERROR'
WHEN '101' THEN 'JOB_STATUS_NORMAL'
WHEN '103' THEN 'JOB_STATUS_ABNORMAL'
WHEN '104' THEN 'JOB_STATUS_SKIPPED'
WHEN '105' THEN 'JOB_STATUS_ORPHAN'
WHEN '106' THEN 'JOB_STATUS_ABORTED'
WHEN '108' THEN 'JOB_STATUS_TIMEOUT'
WHEN '109' THEN 'JOB_STATUS_CANCELLED'
END AS [STATUS]
FROM
dbo.jobmst INNER JOIN
dbo.jobrun ON dbo.jobmst.jobmst_id = dbo.jobrun.jobmst_id
WHERE
dbo.jobrun.jobrun_proddt BETWEEN 'mm/dd/yyyy' AND 'mm/dd/yyyy'
ORDER BY [PROD DATE]
BR,
Derrick Au
