That SQL script could be executed with Control-M Database.
set nocount on
go
declare @limittime varchar ( 8 )
declare @timetest varchar( 6 )
select @limittime=(convert(varchar(20), dateadd(hh,-2,getdate()),108))
select @limittime
select @timetest=(substring (@limittime,1,2)+substring (@limittime,4,2)+substring (@limittime,7,2))
select @timetest
select CMR_AJF.JOBNO, CMR_IOALOG.LOGDATE, CMR_AJF.JOBNAME, CMR_AJF.HOLDFLAG, CMR_IOALOG.LOGTIME
into #forgroupby
from CMR_AJF, CMR_IOALOG
where CMR_AJF.JOBNO=CMR_IOALOG.JOBNO and CMR_IOALOG.MSGID='5401' and CMR_AJF.HOLDFLAG='Y'
ORDER by CMR_IOALOG.LOGDATE,CMR_IOALOG.LOGTIME
select JOBNO, LOGDATE,JOBNAME, HOLDFLAG, max(LOGTIME)
from #forgroupby where LOGTIME < @timetest
group by JOBNO, LOGDATE, JOBNAME, HOLDFLAG
drop table #forgroupby
In case of you want to extract jobs helded since 2 hours for example, you just need to modify the following line :
select @limittime=(convert(varchar(20), dateadd(hh,-2,getdate()),108))
