October 3, 2016 at 2:29 pm
Hi,
I need 4 hrs previous data based on "start_execution_date" column.
I am adding below condition at the end of the query
"start_execution_date >= DATEADD(Hour,-4,start_execution_date)" in below t-sql but, it's not working.
Plz help.
DECLARE @JobDetails TABLE
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
INSERT INTO@JobDetails
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
SELECT * FROM (
SELECTname,
enabled = CASE WHEN enabled = 1 THEN 'Yes' ELSE 'No' END,
date_created,
date_modified,
last_run_date,
start_execution_date = CASE WHEN jd.running = 1 THEN GETDATE() ELSE start_execution_date END,
Status = CASE WHEN jd.running = 1 THEN 'Running' ELSE sjh.Status END,
next_scheduled_run_date,
sj.job_id,
jd.running
FROMmsdb.dbo.sysjobs sj WITH(NOLOCK)
INNER JOIN
(SELECTjob_id,
start_execution_date = MAX(start_execution_date),
next_scheduled_run_date = MAX(next_scheduled_run_date)
FROMmsdb.dbo.sysjobactivity WITH(NOLOCK)
GROUP BY job_id
) sja
ON sj.job_id = sja.job_id
LEFT OUTER JOIN
(SELECTa.job_id,
Status = CASE WHEN run_status = 0 THEN 'Failed'
WHEN run_status = 1 THEN 'Succeeded'
WHEN run_status = 2 THEN 'Retry'
WHEN run_status = 3 THEN 'Cancelled'
WHEN run_status = 4 THEN 'In progress' END
FROMmsdb.dbo.sysjobhistory a WITH(NOLOCK)
INNER JOIN
(SELECTjob_id,
instance_id = MAX(instance_id)
FROMmsdb.dbo.sysjobhistory WITH(NOLOCK)
GROUP BY job_id
) b
ON a.job_id = b.job_id
AND a.instance_id = b.instance_id
) sjh
ON sj.job_id = sjh.job_id
INNER JOIN
@JobDetails jd ON sjh.job_id = jd.job_id
) a
[highlight="Yellow"]WHERE a.start_execution_date >= DATEADD(Hour,-4,a.start_execution_date)[/highlight]
ORDER BY name
Thanks in Advnc.
October 3, 2016 at 3:12 pm
Perhaps something like this:
WHERE a.start_execution_date >= DATEADD(Hour,-4,getdate())
Edit: that's a "greater than or equal" sign above, it keeps messed up when I post.
-- Itzik Ben-Gan 2001
October 3, 2016 at 3:17 pm
Thank you very much Alan.
October 3, 2016 at 3:37 pm
One other thing that could be an issue is that you have this table alias:
FROM msdb.dbo.sysjobhistory a
and this condition that is causing problems:
WHERE a.start_execution_date >= DATEADD(Hour,-4,a.start_execution_date)
start_execution_date is not in the sysjobhistory table. Maybe you meant sysjobactivity.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply