This is a Working Script to Create a Nice Jobs Report
Posted: 13 Jan 2013 2:30
Here is the sql I used to create a really helpful report of all production jobs. The output is raw, and then I import to Excel to pretty it up.
We use SQL Server 2005 and CM 6.4
In Excel I do color banding and formating.
Have fun!!! -- Goo
select distinct
SUBSTRING(c.SCHED_TABLE,3,5) AS 'GROUP'
,UPPER(SUBSTRING(a.JOB_NAME,3,10)) AS 'JOB'
,UPPER(a.MEMNAME) AS 'SCRIPT'
,ISNULL(REPLACE(a.W_DAY_STR,',',' '),'') AS 'WK DAY'
,ISNULL(REPLACE(REPLACE(a.DAYS_AND_OR,'O','OR'),'A','AND'),'') AS 'A/O'
,ISNULL(REPLACE(a.DAY_STR,',',' '),'') AS 'MONTH DAY'
,CASE
WHEN CAST(a.MONTH_1 AS INT) +
CAST(A.MONTH_2 AS INT) +
CAST(A.MONTH_3 AS INT) +
CAST(A.MONTH_4 AS INT) +
CAST(A.MONTH_5 AS INT) +
CAST(A.MONTH_6 AS INT) +
CAST(A.MONTH_7 AS INT) +
CAST(A.MONTH_8 AS INT) +
CAST(A.MONTH_9 AS INT) +
CAST(A.MONTH_10 AS INT) +
CAST(A.MONTH_11 AS INT) +
CAST(A.MONTH_12 AS INT) = 12
THEN
'ALL'
ELSE
case when A.MONTH_1 = 1 then 'JAN ' else '' end +
case when A.MONTH_2 = 1 then 'FEB ' else '' end +
case when A.MONTH_3 = 1 then 'MAR ' else '' end +
case when A.MONTH_4 = 1 then 'APR ' else '' end +
case when A.MONTH_5 = 1 then 'MAY ' else '' end +
case when A.MONTH_6 = 1 then 'JUN ' else '' end +
case when A.MONTH_7 = 1 then 'JUL ' else '' end +
case when A.MONTH_8 = 1 then 'AUG ' else '' end +
case when A.MONTH_9 = 1 then 'SEP ' else '' end +
case when A.MONTH_10 = 1 then 'OCT ' else '' end +
case when A.MONTH_11 = 1 then 'NOV ' else '' end +
case when A.MONTH_12 = 1 then 'DEC ' else '' end
END 'MONTH'
,ISNULL(a.DAYS_CAL + a.WEEKS_CAL,'') AS 'CALENDER'
,CASE ':'
WHEN
ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':'
+ SUBSTRING(a.FROM_TIME,3,2),'')
THEN
''
ELSE
ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':'
+ SUBSTRING(a.FROM_TIME,3,2),'')
END 'START'
,ISNULL(ROUND(CAST(x.AVG_RUN_TIME AS FLOAT)/60,2),'') 'AVG MIN'
,ISNULL(REPLACE(a.DESCRIPTION,',',' '),'') AS ' JOB DESCRIPTION'
,ISNULL(SUBSTRING(e.CONDITION,3,25),'') AS 'IN CONDITION'
,REPLACE(ISNULL((SELECT MAX(d.DESCRIPTION)
FROM
XXX_CTM_EM64.dbo.DEF_VER_JOB d
where ISNULL(SUBSTRING(d.JOB_NAME, 3, LEN(d.JOB_NAME) ),'')
= ISNULL ( SUBSTRING(e.CONDITION, 3,
NULLIF( CHARINDEX( '-', e.CONDITION ) -3,-3) ), '' )
),''),',','')AS 'PRED_DESC'
FROM
XXX_CTM_EM64.dbo.DEF_VER_JOB a
JOIN XXX_CTM_EM64.dbo.DEF_VER_TABLES c
ON ( a.TABLE_ID = c.TABLE_ID )
LEFT OUTER JOIN XXX_CTM_EM64.dbo.AVG_RUN_INFO x
ON ( a.JOB_NAME = x.JOB_MEM_NAME )
LEFT OUTER JOIN XXX_CTM_EM64.dbo.DEF_VER_LNKI_P e
ON ( a.TABLE_ID = e.TABLE_ID AND a.JOB_ID = e.JOB_ID )
WHERE
c.DATA_CENTER LIKE '%PROD%'
AND c.DATA_CENTER NOT LIKE '%CANCEL%'
AND UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
AND a.IS_CURRENT_VERSION = 'Y'
ORDER BY
1,2
We use SQL Server 2005 and CM 6.4
In Excel I do color banding and formating.
Have fun!!! -- Goo
select distinct
SUBSTRING(c.SCHED_TABLE,3,5) AS 'GROUP'
,UPPER(SUBSTRING(a.JOB_NAME,3,10)) AS 'JOB'
,UPPER(a.MEMNAME) AS 'SCRIPT'
,ISNULL(REPLACE(a.W_DAY_STR,',',' '),'') AS 'WK DAY'
,ISNULL(REPLACE(REPLACE(a.DAYS_AND_OR,'O','OR'),'A','AND'),'') AS 'A/O'
,ISNULL(REPLACE(a.DAY_STR,',',' '),'') AS 'MONTH DAY'
,CASE
WHEN CAST(a.MONTH_1 AS INT) +
CAST(A.MONTH_2 AS INT) +
CAST(A.MONTH_3 AS INT) +
CAST(A.MONTH_4 AS INT) +
CAST(A.MONTH_5 AS INT) +
CAST(A.MONTH_6 AS INT) +
CAST(A.MONTH_7 AS INT) +
CAST(A.MONTH_8 AS INT) +
CAST(A.MONTH_9 AS INT) +
CAST(A.MONTH_10 AS INT) +
CAST(A.MONTH_11 AS INT) +
CAST(A.MONTH_12 AS INT) = 12
THEN
'ALL'
ELSE
case when A.MONTH_1 = 1 then 'JAN ' else '' end +
case when A.MONTH_2 = 1 then 'FEB ' else '' end +
case when A.MONTH_3 = 1 then 'MAR ' else '' end +
case when A.MONTH_4 = 1 then 'APR ' else '' end +
case when A.MONTH_5 = 1 then 'MAY ' else '' end +
case when A.MONTH_6 = 1 then 'JUN ' else '' end +
case when A.MONTH_7 = 1 then 'JUL ' else '' end +
case when A.MONTH_8 = 1 then 'AUG ' else '' end +
case when A.MONTH_9 = 1 then 'SEP ' else '' end +
case when A.MONTH_10 = 1 then 'OCT ' else '' end +
case when A.MONTH_11 = 1 then 'NOV ' else '' end +
case when A.MONTH_12 = 1 then 'DEC ' else '' end
END 'MONTH'
,ISNULL(a.DAYS_CAL + a.WEEKS_CAL,'') AS 'CALENDER'
,CASE ':'
WHEN
ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':'
+ SUBSTRING(a.FROM_TIME,3,2),'')
THEN
''
ELSE
ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':'
+ SUBSTRING(a.FROM_TIME,3,2),'')
END 'START'
,ISNULL(ROUND(CAST(x.AVG_RUN_TIME AS FLOAT)/60,2),'') 'AVG MIN'
,ISNULL(REPLACE(a.DESCRIPTION,',',' '),'') AS ' JOB DESCRIPTION'
,ISNULL(SUBSTRING(e.CONDITION,3,25),'') AS 'IN CONDITION'
,REPLACE(ISNULL((SELECT MAX(d.DESCRIPTION)
FROM
XXX_CTM_EM64.dbo.DEF_VER_JOB d
where ISNULL(SUBSTRING(d.JOB_NAME, 3, LEN(d.JOB_NAME) ),'')
= ISNULL ( SUBSTRING(e.CONDITION, 3,
NULLIF( CHARINDEX( '-', e.CONDITION ) -3,-3) ), '' )
),''),',','')AS 'PRED_DESC'
FROM
XXX_CTM_EM64.dbo.DEF_VER_JOB a
JOIN XXX_CTM_EM64.dbo.DEF_VER_TABLES c
ON ( a.TABLE_ID = c.TABLE_ID )
LEFT OUTER JOIN XXX_CTM_EM64.dbo.AVG_RUN_INFO x
ON ( a.JOB_NAME = x.JOB_MEM_NAME )
LEFT OUTER JOIN XXX_CTM_EM64.dbo.DEF_VER_LNKI_P e
ON ( a.TABLE_ID = e.TABLE_ID AND a.JOB_ID = e.JOB_ID )
WHERE
c.DATA_CENTER LIKE '%PROD%'
AND c.DATA_CENTER NOT LIKE '%CANCEL%'
AND UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
AND a.IS_CURRENT_VERSION = 'Y'
ORDER BY
1,2