April 2, 2008 at 7:00 am
I have a table like this
declare @jobhistory table( instance_id int not null primary key, job_id uniqueidentifier not null,start_timestamp decimal not null , run_duration int not null);
sample row can look like this
44907927F85D9-4750-40CF-BE61-D70376E1BE79 20080402020000 1
i need to work out what rows have a start_timestamp between start_timestamp and start_timestamp + run_duration in other rows
thats how i do it
select *
from @jobhistory t1
cross join @jobhistory t2
where t1.start_timestamp between t2.start_timestamp and (t2.start_timestamp + t2.run_duration)
and t1.instance_id<>t2.instance_id
how do i remove duplicates?
i'm ending up with the following results
row 1 t1 values followed by t2 values
row 2 t2 values followed by t1 values
April 2, 2008 at 9:13 am
Max,
Not use the CROSS JOIN ...by definition CROSS JOIN will do the following:
Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table.
Thus your results will display the values from your first table then again for your second table...
April 2, 2008 at 9:20 am
even if i do the inner join i'm getting the same
all i'm trying to do is to find out what jobs overlap each other. In this case i need to know that while job A was running job C was kicked off as well and was doing some job on the same DB. I have created the list of all jobs, i had to convert the timestamp values
insert @jobhistory(instance_id,job_id,start_timestamp,run_duration)
select
instance_id --Unique identifier for the row.
,job_id -- unique Job ID.
,case
when len(run_time)=6 then cast ((cast (run_date AS VARCHAR (8))+ cast (run_time as varchar(6))) as decimal)
when len(run_time)=5 then cast ((cast (run_date AS VARCHAR (8))+'0'+ cast (run_time as varchar(6)))as decimal)
when len(run_time)=4 then cast ((cast (run_date AS VARCHAR (8))+'00'+ cast (run_time as varchar(6)))as decimal)
when len(run_time)=3 then cast ((cast (run_date AS VARCHAR (8))+'000'+ cast (run_time as varchar(6)))as decimal)
when len(run_time)=2 then cast ((cast (run_date AS VARCHAR (8))+'0000'+ cast (run_time as varchar(6)))as decimal)
when len(run_time)=1 then cast ((cast (run_date AS VARCHAR (8))+'00000'+ cast (run_time as varchar(6)))as decimal)
end as start_timestamp--Date the job or step started execution.
,run_duration--Elapsed time in the execution of the job or step in HHMMSS format.
from msdb.dbo.sysjobhistory with (nolock)
where step_id=1 -- skip job outcomes (step_id=0)
but now although i can see those overlapping jobs they come in paires :))
April 2, 2008 at 9:30 am
If you take your original query and replace CROSS JOIN with INNER JOIN and ON ....
What is the output?
April 2, 2008 at 9:32 am
exactly the same:D
April 2, 2008 at 9:40 am
Fun huh?
Just trying to troubleshoot how about UNION
Split the query into two seperate queries from the two tables then UNION them to merge the results...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply