job failure report
begin tran DECLARE @PreviousDate datetime DECLARE @Year VARCHAR(4) DECLARE @Month VARCHAR(2) DECLARE @MonthPre VARCHAR(2) DECLARE @Day VARCHAR(2) DECLARE @DayPre VARCHAR(2) DECLARE @FinalDate INT -- Initialize Variables -- set number of days here SET @PreviousDate = DATEADD(dd, -10, GETDATE()) -- Last 1 day SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT) -- Final Logic SELECT j.[name], s.step_name, h.step_id, h.step_name, h.run_date, h.run_time, ...