Need alternative solution for the query involving CTE and temp tables

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

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

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