Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All
Needed help regarding a query.The query needs to display the concurrency list for jobs. The table has the following fields:-
Job_Name | Start_time | End_time | Execution_time | status

I got to display the list of those jobs which started at a time lesser than the start time of a specific job selected and ends up in a time less than, the current End_time of the job.I tried to use a CTE for this, but seems like logical error, And its taking up a very long time to execute the query.

Both START_TIME AND END_TIME Are in combined date-time format:-2016-04-30 00:32:40 00000000
I am not sure how to attach screenshots in this forum as well! :(

What I have tried:

SQL
WITH TIME_CTE(JOB_NAME,START_TIME,END_TIME,ST_TIME,E_TIME)
As
-- Define the CTE query.
(
    SELECT JOB_NAME,START_TIME,END_TIME,CAST(START_TIME as time)as ST_TIME, CAST(END_TIME as time) as E_TIME
    FROM COMP_HIS_TBL where START_TIME like '2016-04-30%'
)
-- Define the outer query referencing the CTE name.
SELECT *
FROM TIME_CTE
JOIN (Select JOB_NAME,CAST(START_TIME as Time) as STRT_TIME , CAST(END_TIME as time) as ED_TIME from [COMP_HIS_TBL])as A 
ON TIME_CTE.ST_TIME
Posted
Updated 8-May-16 21:51pm
v2
Comments
George Jonsson 8-May-16 23:55pm    
I have troubles understanding your question. Can you give a practical example with the times you want to compare?

Also, what type do your Start_Time and End_time columns have? DATETIME or VARCHAR?
mousau 9-May-16 2:28am    
Hi George
Thanks a lot for responding back. The START_TIME and END_TIME are of Datatype:-"datetime2"(using SQL sever 2012). The existing table consists of Data related to the specified fields:-

JOB_NAME | RUN_ID | START_TIME | END_TIME | EXECUTION_TIME|STATUS |

JOB_BW_varYR_PD7 | 1121 | 2016-04-30 13:08:14.0000000| 2016-04-30 13:25:21.0000000 |1027.000000 |SUCCESS|

Like wise there are various jobs. Now there are certain long running jobs and which either ended up with the status : failure or success , across specific days. Now the need is what were the other jobs which were running during that time period when a given job was running.
Hence the query is :"display the list of all those jobs which were running across on a given day(when the given job was running),such that, the START_TIME OF the Jobs should be less than the START_TIME of the current job(chosen) and the END_TIME should be less than the END_TIME of the current Job."
The above query helps us to find out the list of active and inactive jobs when a specific job was running. Alternative approaches will also be welcomed.
Thank you
Moumita
Tomas Takac 9-May-16 2:37am    
So you have a id or name of the long running job and want to list all the jobs that were running during the execution of that long running job. Is that correct?
mousau 9-May-16 2:42am    
Hi Tom
Thanks.Yes you are right.
Thank you
chaau 9-May-16 2:37am    
Please provide sample data and an expected result.

1 solution

To get a list of jobs that were running during the execution of another job you can use a simple join:
SQL
declare @job varchar(100) = 'main'

select b.* 
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on b.Start_Time <= a.End_Time
and b.End_Time >= a.Start_Time
and b.Job_name <> a.Job_Name
where a.Job_name = @job

a is the main job, b are all the other jobs that were running during that time. You just need to compare the start and end times. Please note that this gets more complicated if you consider null values, jobs that didn't finish yet for example.

Here is the SQL Fiddle[^].
 
Share this answer
 
Comments
mousau 9-May-16 4:38am    
Hi Tom
Thanks a lot.Its good,but one problem the START_TIME is of datatype datetime2, and i need the time value alone for comparision. so how could the CAST operator be useful??
Tomas Takac 9-May-16 4:52am    
I don't understand. Why would you only compare times?
mousau 9-May-16 5:02am    
Hi Tom,
Well coz that START_TIME fields consists of date and time both. If i try to get the count of these jobs on the whole:- tried: declare @job varchar(100) ='JOB_BW_varYR_PD7'
select b.JOB_NAME,b.REPOSITORY_NAME,b.START_TIME,b.END_TIME,Count(JOB_NAME)
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on Cast(b.START_TIME as Time) <= Cast(a.END_TIME as Time)
and Cast(b.END_TIME as Time)>= Cast(a.START_TIME as time)
and b.JOB_NAME <> a.JOB_NAME
where a.JOB_NAME = @job


but its throwing "Ambiguous Column name" for JOB_NAME!!!:(:(:(
Tomas Takac 9-May-16 5:19am    
I still don't see why you want to omit the date. Yesterday's run of the job is different from today's run. Each time different jobs may overlap. This changes over the history of the job executions. IMO date is important. But as I said in a different comment I probably don't understand what you want.
mousau 9-May-16 5:40am    
Hi Tom
Your logic is right, well i gotta clarify this with my seniors.Thanks for the help. Thanks a lot:)
Moumita

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900