Query to list schedule details for SQL Server Agent Jobs

Kinda big, but this is a helpful query for listing scheduling details for SQL Server Agent jobs.  I'm doing a multi-table join between sysjobs, sysjobschedules and sysschedules, returning JobName, Enabled, Frequency, NextRunDate, RunTime and ScheduleDetails.

Here's a slice from my customer's data:

Let me know what you think.

  -- return agent job schedule details

  USE msdb;

  SELECT

    sj.Name [Job Name],

    CASE sj.enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END [Enabled],

    CASE syssc.freq_type

      WHEN 1 THEN 'Once'

      WHEN 4 THEN 'Daily'

      WHEN 8 THEN 'Weekly'

      WHEN 16 THEN 'Monthly'

      WHEN 32 THEN 'Monthly relative'

      WHEN 64 THEN 'When SQLServer Agent starts' END [Frequency],

    CASE syssc.active_start_date WHEN 0 THEN NULL

      ELSE SUBSTRING(CONVERT(VARCHAR(15),syssc.active_start_date),1,4) + '/' +

       SUBSTRING(CONVERT(VARCHAR(15),syssc.active_start_date),5,2) + '/' +

       SUBSTRING(CONVERT(VARCHAR(15),syssc.active_start_date),7,2) END [NextRunDate],

    CASE LEN(syssc.active_start_time)

      WHEN 1 THEN CAST('00:00:0' + RIGHT(syssc.active_start_time,2) AS CHAR(8))

      WHEN 2 THEN CAST('00:00:' + RIGHT(syssc.active_start_time,2) AS CHAR(8))

      WHEN 3 THEN CAST('00:0' + LEFT(RIGHT(syssc.active_start_time,3),1) +':' +

 RIGHT(syssc.active_start_time,2) AS CHAR (8))

      WHEN 4 THEN CAST('00:' + LEFT(RIGHT(syssc.active_start_time,4),2) +':' +

 RIGHT(syssc.active_start_time,2) AS CHAR (8))

      WHEN 5 THEN CAST('0' + LEFT(RIGHT(syssc.active_start_time,5),1) +':' +

 LEFT(RIGHT(syssc.active_start_time,4),2) 

         +':' + RIGHT(syssc.active_start_time,2) AS CHAR (8))

      WHEN 6 THEN CAST(LEFT(RIGHT(syssc.active_start_time,6),2) +':' +

 LEFT(RIGHT(syssc.active_start_time,4),2) 

         +':' + RIGHT(syssc.active_start_time,2) AS CHAR (8)) END [RunTime],

    dbo.udf_schedule_description(syssc.freq_type,

         syssc.freq_interval, 

         syssc.freq_subday_type,

         syssc.freq_subday_interval,

         syssc.freq_relative_interval, 

         syssc.freq_recurrence_factor,

         syssc.active_start_date,

         syssc.active_end_date, 

         syssc.active_start_time,

         syssc.active_end_time) [ScheduleDetails]

  FROM

  dbo.sysjobs sj LEFT OUTER JOIN dbo.sysjobschedules sjs

           ON sj.job_id = sjs.job_id INNER JOIN dbo.sysschedules syssc

             ON sjs.schedule_id = syssc.schedule_id LEFT OUTER JOIN (

                 SELECT job_id, MAX(run_duration) [run_duration]

                 FROM dbo.sysjobhistory

                 GROUP BY job_id

                ) q1

                 ON sj.job_id = q1.job_id

  WHERE

       sjs.next_run_time = 0

UNION

  SELECT

     sj.Name [JobName],

     CASE sj.Enabled    WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END [Enabled],

     CASE syssc.freq_type

         WHEN 1 THEN 'Once'

         WHEN 4 THEN 'Daily'

         WHEN 8 THEN 'Weekly'

         WHEN 16 THEN 'Monthly'

         WHEN 32 THEN 'Monthly relative'

         WHEN 64 THEN 'When SQLServer Agent starts' END [Frequency],

     CASE sjs.next_run_date WHEN 0 THEN NULL

         ELSE SUBSTRING(CONVERT(VARCHAR(15),next_run_date),1,4) + '/' +

          SUBSTRING(CONVERT(VARCHAR(15),next_run_date),5,2) + '/' +

          SUBSTRING(CONVERT(VARCHAR(15),next_run_date),7,2) END [NextRundate],

     CASE LEN(sjs.next_run_time)

         WHEN 1 THEN CAST('00:00:0' + RIGHT(next_run_time,2) AS CHAR(8))

         WHEN 2 THEN CAST('00:00:' + RIGHT(next_run_time,2) AS CHAR(8))

         WHEN 3 THEN CAST('00:0' + LEFT(RIGHT(next_run_time,3),1) +':' +                   

            RIGHT(next_run_time,2) AS CHAR (8))

         WHEN 4 THEN CAST('00:' + LEFT(RIGHT(next_run_time,4),2)  +':' +         

            RIGHT(next_run_time,2) AS CHAR (8))

         WHEN 5 THEN CAST('0' + LEFT(RIGHT(next_run_time,5),1) +':' +

            LEFT(RIGHT(next_run_time,4),2) 

                +':' + RIGHT(next_run_time,2) AS CHAR (8))

         WHEN 6 THEN CAST(LEFT(RIGHT(next_run_time,6),2) +':' +

            LEFT(RIGHT(next_run_time,4),2) 

                +':' + RIGHT(next_run_time,2) AS CHAR (8)) END [RunTime],

         syssc.freq_interval, 

         syssc.freq_subday_type,

         syssc.freq_subday_interval,

         syssc.freq_relative_interval, 

         syssc.freq_recurrence_factor,

         syssc.active_start_date,

         syssc.active_end_date, 

         syssc.active_start_time,

         syssc.active_end_time) [ScheduleDetails]

   FROM

      dbo.sysjobs sj LEFT OUTER JOIN dbo.sysjobschedules sjs

        ON sj.job_id = sjs.job_id INNER JOIN dbo.sysschedules syssc

          ON sjs.schedule_id = syssc.schedule_id LEFT OUTER JOIN (

               SELECT job_id, MAX(run_duration) [run_duration]

               FROM dbo.sysjobhistory

               GROUP BY job_id

               ) q1

              ON sj.job_id = Q1.job_id

   WHERE

      sjs.next_run_time <> 0

   ORDER BY

      RunTime