I meant to get all of the status code from db like Tyler said.
I have the following query and I would like to get, by select query, all relevant system return code and not declare temp table:
(I would like to do a daily checking for jobs in activities with specific status)
declare @statusTable table
(
systemReturnCode int,
taskStatusName varchar(1000)
)
insert into @statusTable values (1543,'Unknown')
insert into @statusTable values (1560,'Workflow is blocked.')
insert into @statusTable values (1561,'STOP - Client-wide stop of automatic processing.')
insert into @statusTable values (1562,'HELD - Manual stop has been set.')
insert into @statusTable values (1563,'STOP - Automatic processing has been stopped.')
insert into @statusTable values (1564,'STOP - Queue processing has been stopped.')
insert into @statusTable values (1852,'Rejected')
select EH.EH_AH_Idnr as jobRunID,
EH.EH_Name as jobName,
EH.EH_OType as jobType,
EH.EH_Status as statusId,
status.taskStatusName as statusName,
EH.EH_StartTime as jobStartTime,
EH.EH_Client as clientNumber,
EH.EH_RunTime as runTimeInSeconds,
USR.USR_FirstName + ' ' + usr.USR_LastName as userFullName
from EH inner join
USR on EH.EH_USR_Idnr = USR.USR_OH_Idnr inner join
@statusTable status on EH.EH_Status = status.systemReturnCode
UNION ALL
select AH_Idnr as jobRunID,
AH_Name as jobName,
AH_OType as jobType,
AH_Status as stausId,
null as statusName,
AH_TimeStamp2 as jobStartTime,
AH_Client as cilentNumber,
AH_RunTime as runtimeInsSeconds,
USR.USR_FirstName + ' ' + USR.USR_LastName as userFullName
from AH inner join
USR on AH.AH_USR_Idnr = USR.USR_OH_Idnr inner join
@statusTable status on AH.AH_Status = status.systemReturnCode
--DATEDIFF should retrive jobs were active in last day
where DATEDIFF(DAY,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()),ah.AH_TimeStamp2 ) ,GETDATE())<=1
Thanks,
Idan