uspLoadJobHistory

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspLoadJobHistory') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspLoadJobHistory
GO

--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspLoadJobHistory(@Date date)
as
begin
set nocount on;

declare
@result int = 0,
@CtsJobHistoryHeaderId dbo.CtsJobHistoryHeaderId


if exists (select * from dbo.CtsJobHistoryHeader where RunDate = @Date)
begin
return @result
end

MERGE dbo.CtsJob AS tgt
USING (
select
job_id, name, [enabled],
IsSchedule = CASE WHEN [enabled] = 1 AND
EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules js, msdb.dbo.sysschedules s
WHERE js.schedule_id = s.schedule_id and js.job_id = j.job_id and s.[enabled] = 1)
THEN 1
ELSE 0
END
from msdb.dbo.sysjobs j
) AS src
ON tgt.JobId = src.job_id
WHEN MATCHED THEN
UPDATE SET JobName = src.name, IsActive = [enabled]
WHEN NOT MATCHED BY TARGET THEN
INSERT (JobId, JobName, IsActive, ShowInReport) VALUES (job_id, name, [enabled], IsSchedule);

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO dbo.CtsJobHistoryHeader(RunDate) values (@Date)
SELECT @CtsJobHistoryHeaderId = SCOPE_IDENTITY();

WITH cteJob(job_id, run_date, run_time) as
(
select
job_id,
run_date = min(run_date),
run_time = max(run_time)
from msdb.dbo.sysjobhistory
where CAST(CAST(run_date as varchar) AS DATE) = @Date
and step_id = 0
group by job_id
), scheduleJob (job_id) as
(
select distinct
j.job_id
from msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
JOIN msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
where dbo.fnIsSchduledForDate(@Date, s.[enabled], s.freq_type, s.freq_interval, s.freq_relative_interval, s.active_start_date, s.active_end_date) = 1
)
INSERT INTO dbo.CtsJobHistoryDetail(CtsJobHistoryHeaderId, CtsJobId, RunTime, RunDuration, RunStatus, IsScheduled)
SELECT
@CtsJobHistoryHeaderId,
CtsJob.CtsJobId,
Run_time = CAST(DATEADD(ss, hist.run_time % 100, DATEADD(mi, (hist.run_time / 100) % 100, DATEADD(hh, hist.run_time / 10000, '00:00:00'))) AS TIME),
RunDuration = (hist.run_duration / 10000) * 3600 + ((hist.run_duration / 100) % 100) * 60 + hist.run_duration % 100,
hist.run_status,
CASE WHEN sj.job_id IS NOT NULL THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobhistory hist
JOIN cteJob j ON hist.job_id = j.job_id and hist.step_id = 0 and hist.run_date = j.run_date and hist.run_time = j.run_time
JOIN dbo.CtsJob ON hist.job_id = CtsJob.JobId
LEFT JOIN scheduleJob sj ON hist.job_id = sj.job_id

COMMIT TRANSACTION
END TRY
BEGIN CATCH
declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    select
@ErrorMessage = ERROR_MESSAGE(), --+ ' Line ' + cast(ERROR_LINE() as nvarchar(5)),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

if @@TRANCOUNT > 0
begin
rollback transaction
end

    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
set @result = 1
END CATCH

return @result
end
--------------------------------------------------------------------------------
GO

/*
declare @date date = '2018-01-01'
while @date < dateadd(dd, -1, getdate())
begin
exec uspLoadJobHistory @date
set @date = dateadd(dd, 1, @date)
end
*/
--select * from dbo.CtsJob