WEB LISTING 2: Excerpt from Corrected Version of Stored Procedure usp_Scheduled_Jobs

AVG( CASE     -- Average Run Time (Convert everything to seconds = HH*3600 + MM*60 + SS)
        WHEN LEN( CAST(jh.run_duration AS varchar) ) > 5 THEN                 -- Format: HHMMSS
                    CAST(LEFT(CAST(jh.run_duration as varchar), 2) AS int) * 3600       + -- get hours
                    CAST(SUBSTRING(CAST(jh.run_duration as varchar), 3, 2) AS int) * 60 + -- get minutes
                    CAST(RIGHT(CAST(jh.run_duration as varchar), 2) AS int)               -- get seconds
        WHEN LEN( CAST(jh.run_duration AS varchar) ) = 5 THEN                    -- Format: HMMSS
                    CAST(LEFT(CAST(jh.run_duration as varchar), 1) AS int) * 3600       + -- get hours
                    CAST(SUBSTRING(CAST(jh.run_duration as varchar), 2, 2) AS int) * 60 + -- get minutes
                    CAST(RIGHT(CAST(jh.run_duration as varchar), 2) AS int)               -- get seconds
        WHEN LEN( CAST(jh.run_duration AS varchar) ) = 4 THEN                    -- Format: MMSS
                    CAST(LEFT(CAST(jh.run_duration as varchar), 2) AS int) * 60 +         -- get minutes
                    CAST(RIGHT(CAST(jh.run_duration as varchar), 2) AS int)               -- get seconds
        WHEN LEN( CAST(jh.run_duration AS varchar) ) = 3 THEN                    -- Format: MSS
                    CAST(LEFT(CAST(jh.run_duration as varchar), 1) AS int) * 60 +         -- get minutes
BEGIN CALLOUT A
                    CAST(RIGHT(CAST(jh.run_duration as varchar), 2) AS int)               -- get seconds
END CALLOUT A
         ELSE                                                                     -- Format: SS or S
                     jh.run_duration                                                       -- get seconds
END ),