Finding failed Job history using SQL SERVER

I have used following query to find scheduled job history in sql server Agent. This query returns last 7 days failed jobs detail with max fail date for each job.

-- Variable Declarations

 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
@PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 1 days

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],

max(h.run_date) run_date,

max(h.run_time) run_time,

h.message,

h.server

FROM
msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j

ON h.job_id = j.job_id

INNER JOIN msdb.dbo.sysjobsteps s

ON j.job_id = s.job_id

AND h.step_id = s.step_id

WHERE
h.run_status = 0 -- Failure

AND h.run_date > @FinalDate

group
by

j.[name],

h.message,

h.server

--ORDER BY h.instance_id DESC