Sistemde belirli aralıklar ile sistemde çalışan fazla job bulunmaktadır. Sql server database de bulunan jobları iki tarih arasında çalışanları filtreleyerek nasıl bulabilirim.

Aşağıdaki query ile iki tarih arasında çalışan jobları ve durumlarını bulabilirsin.

SELECT distinct
      cast([sJOB].[job_id] as varchar(max)) AS ID 
    , [sJSTP].[step_name] AS STEP_ADI
    , [sJOB].[name] AS JOB_ADI
    , CASE [sJSTP].[run_date]
        WHEN 0 THEN NULL
        ELSE
            CAST(
                CAST([sJSTP].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS BAŞLANGIÇ,
      dateadd(ss, run_duration,   CASE [sJSTP].[run_date]
        WHEN 0 THEN NULL
        ELSE
            CAST(
                CAST([sJSTP].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END) BİTİŞ 
    , CASE [sJSTP].[run_status] 
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Success'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 5 THEN 'Unknown'
      END AS DURUM
FROM
    [msdb].[dbo].[sysjobhistory] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    inner join [msdb].[dbo].[sysjobsteps] steps
        ON [sJSTP].[job_id] = [steps].[job_id]
where [sJSTP].[run_date] <> 0
 and CASE [sJSTP].[run_date]
        WHEN 0 THEN NULL
        ELSE
            CAST(
                CAST([sJSTP].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END  between dateadd(hh, -20, getdate()) and getdate()
 and [sJSTP].[step_name] not in ('(Job outcome)')
 order by 4 desc