Click here to Skip to main content
15,943,623 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All
I need to display the count of no of jobs starting at the Start_time and End_time of the jobs present in the table.The table structure consists of :

RepoName|JobName | RunID | StartTime|EndTime |ExecutionTime|Status|


The Expected output should be in the format:-

RunID|JobName|Start_time|End_Time|JobsCount at StartTime|JobsCount at EndTime|


What I have tried:

Following was query tried which involves CTE and Temp tables:-

declare @count int=1,

@max int,

@stime datetime,

@endtime datetime,

@runid int,

@jobname varchar(1000)

;




with cte as(

select distinct RUN_ID,JOB_NAME,START_TIME,END_TIME,EXECUTION_TIME from COMP_HIS_TBL(nolock)

where STATUS='Failure'




)

,temp_tab as(

select ROW_NUMBER() over(order by run_id)row_number,* from cte)

select * into #temp from temp_tab

order by 1,EXECUTION_TIME desc

select @max=MAX(ROW_NUMBER) from #temp

while (@count<=@max)

begin

select @stime=start_time,@endtime=end_time,@runid=run_id,@jobname=job_name from #temp where

ROW_NUMBER=@count

select

@runid RUN_ID ,@jobname JOB_NAME,@starttime Start_time ,@endtime end_time

select count(RUN_ID) as JOBS_AT_START_TIME  from COMP_HIS_TBL(nolock)

where  @stime between START_TIME  and END_TIME 

select count(RUN_ID) as JOBS_AT_END_TIME  from COMP_HIS_TBL(nolock)

where  @endtime between START_TIME  and END_TIME 

set @count=@count+1

end




--DROP TABLE #TEMP


Which is working fine for few jobs, but ending up with the warning "Query completed with errors!! and ending up with hardly "1 row retrieved". Please provide any alternative solution to this. The same requirement is not possible by other ways like Joins and others??
Posted
Updated 9-May-16 21:12pm

1 solution

Correlated sub-queries come to my mind.
SQL
SELECT RUN_ID, JOB_NAME, START_TIME, END_TIME,
(
    SELECT COUNT(*) FROM COMP_HIS_TBL on_start 
    WHERE on_start.RUN_ID <> failed.RUN_ID
    AND failed.START_TIME BETWEEN on_start.START_TIME AND on_start.END_TIME
) AS 'JobsCount at StartTime',
(
    SELECT COUNT(*) FROM COMP_HIS_TBL on_end 
    WHERE on_end.RUN_ID <> failed.RUN_ID
    AND failed.END_TIME BETWEEN on_end.START_TIME AND on_end.END_TIME
) AS 'JobsCount at EndTime'
FROM COMP_HIS_TBL failed
WHERE failed.STATUS = 'Failure'

I didn't run this so there may be some bugs.
 
Share this answer
 
v2
Comments
mousau 10-May-16 4:52am    
Hi Tom
Thanks for the response. I tried using the sql but its displaying syntax errors,wherever the simple braces are there.I dont understand the reason, coz syntax wise it seems ok:-
SELECT RUN_ID, JOB_NAME, START_TIME, END_TIME,
(
SELECT COUNT(*) FROM COMP_HIS_TBL on_start
WHERE on_start.RUN_ID <> failed.RUN_ID
AND failed.START_TIME BETWEEN on_start.START_TIME AND on_start.END_TIME AND
) AS JobsCountatStartTime,
(
SELECT COUNT(*) FROM COMP_HIS_TBL on_end
WHERE on_end.RUN_ID <> failed.RUN_ID
AND failed.END_TIME BETWEEN on_end.START_TIME AND on_end.END_TIME AND
) AS JobsCountatEndTime
FROM COMP_HIS_TBL failed
WHERE failed.STATUS = 'Failure'


Its throwing syntax error:-


"Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.
"
Tomas Takac 10-May-16 6:01am    
You are right, there was a syntax error. Extra AND in the sub-queries. I updated the solution, please try now.
mousau 10-May-16 6:54am    
Hi Tom
Wow,Thanks a lot,Iam not well versed with the concepts of CTE and Temp tables ,just aware about it.Extremely thankful for the help.It has worked.Feeling relieved.Thanks a lot for the above solution.

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