DECLARE @command varchar(8000),
        @msgBody varchar (4000),
        @withJobOutcome VARCHAR(1),
        @APJobExecTime DATETIME,
        @msgSubject varchar(200),
        @reportName varchar(200),
        @rundate DATE

-- Set report date parameter
-- Set @withJobOutcome as 'T' to retrieve history of job step 'Job Outcome' as well.
SELECT @rundate = '2013-7-22', @withJobOutcome = 'F'

-- This file name is specific to DB23, please provide an available
-- path respect to the server you want to monitor.
SET @reportName = 'E:\Report\JobExecutionHistory_' + CONVERT(CHAR(10), @rundate, 120) + '.xls'

-- Remove possible report file
SELECT @command = 'del ' + @reportName
EXEC master..xp_cmdshell @command, NO_OUTPUT

-- Get execution date time of Job A0000SQ-AP
SELECT TOP 1 @APJobExecTime = CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS DATETIME), 120) +
       CAST(STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)),6),5,0,':'),3,0,':') AS DATETIME)
  FROM sysjobhistory h
 INNER JOIN sysjobs j
    ON j.job_id = h.job_id
 WHERE CAST(STR(h.run_date,8, 0) AS DATE)= @rundate
   AND j.name = 'A0000SQ-AP'

-- Build Job Execution History Query Statement
SELECT @command ='bcp " SELECT ''Job Name'' JobName, ''Step Name'' StepName, '' Execution Time'' RunDate, ''Step Duration'' StepDuration, ''Execution Status'' ExecutionStatus, ''Shift'' Shift UNION ALL SELECT JobName, StepName, CONVERT(CHAR(19), RunDate, 120), StepDuration, ExecutionStatus,(CASE WHEN RunDate < ''' + CONVERT(char(19), @APJobExecTime, 120) + ''' THEN ''NA'' WHEN RunDate >= ''' + CONVERT(char(19), @APJobExecTime, 120) + ''' THEN ''AP'' END) AS Shift FROM (SELECT j.name JobName, h.step_name StepName, CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS DATETIME), 120) + CAST(STUFF(STUFF(RIGHT(''000000'' + CAST(h.run_time AS VARCHAR(6)),6),5,0,'':''),3,0,'':'') AS DATETIME) RunDate, LEFT(RIGHT(''000000'' + CAST(h.run_duration AS VARCHAR(10)),6),2) + '':'' + SUBSTRING(RIGHT(''000000'' + CAST(h.run_duration AS VARCHAR(10)),6),3,2) + '''''''' + RIGHT(RIGHT(''000000'' + CAST(h.run_duration AS VARCHAR(10)),6),2) + '''''''''''' StepDuration, (CASE h.run_status WHEN 0 THEN ''failed'' WHEN 1 THEN ''Succeded'' WHEN 2 THEN ''Retry'' WHEN 3 THEN ''Cancelled'' WHEN 4 THEN ''In Progress'' END) AS ExecutionStatus, h.message MessageGenerated FROM msdb..sysjobhistory h INNER JOIN msdb..sysjobs j ON j.job_id = h.job_id WHERE CAST(STR(h.run_date,8, 0) AS DATE)= ''' + CONVERT(CHAR(10), @rundate, 120) + ''' AND h.step_name not like (SELECT TOP 1 CASE ''' + @withJobOutcome + ''' WHEN ''T'' THEN '''' WHEN ''F'' THEN ''(Job outcome)'' END FROM msdb..sysjobs) AND (j.name in (''A0000SQ-NA'',''A0000SQ-AP'') or (j.name like ''A0%'' and j.name not like ''%-%''))) job ORDER BY RunDate, JobName, StepName" queryout ' + @reportName + ' -c -T -S ' + @@SERVERNAME

-- Export to Excel file
EXEC master..xp_cmdshell @command

-- Send email with the report as attachement
SET @msgBody = 'Job Execution Time history report on ' + CONVERT(CHAR(10), @rundate, 120) + ' is ready, please look detail information by checking the attached report file.'
SET @msgSubject = 'Job Execution History Report for [' + CONVERT(CHAR(10), @rundate, 120) + ']'
EXEC msdb..sp_send_dbmail @profile_name = 'Notifications',
                          @recipients = 'xxxxx@gmail.com',
                          @subject = @msgSubject,
                          @body = @msgBody,
                          @body_format ='TEXT',
                          @file_attachments = @reportName

-- Remove the temporary file after email sent
SELECT @command = 'del ' + @reportName
EXEC master..xp_cmdshell @command, NO_OUTPUT