sql - 案例计数未返回预期信息

Question

我每天早上都有一个自动检查脚本，只要它们不属于“预期运行的作业”类别，用户就会被告知当前正在运行的作业数量。

由于当前的计数分别为 4 和 3 ，因此我预计StatusID会是 GREENIMPORT和 AMBERBATCH

我目前的代码是

DECLARE @Datecreated DATETIME = GetDate())

DECLARE @JobInfo AS TABLE
(
 JobType INT,
 JobID NVARCHAR(30),
 StatusID NVARCHAR(30),
 Message NVARCHAR(500),
 DateCreated DATETIME,
 ITEMS INT
)

INSERT INTO @JobInfo (JobType,JobID,StatusID,Message, DateCreated,ITEMS)
SELECT
0 as Jobtype,
'BATCH' AS JobID,
CASE WHEN Count(CASE JobTypeID WHEN 1 THEN 0 WHEN 30 THEN 0 WHEN 234 THEN 0 ELSE 1 end) >0 THEN N'AMBER' ELSE N'GREEN' END,
'Jobs running longer than 1 hour (ITEMS)',
CAST( @DateCreated AS NVARCHAR(30)),
COUNT(CASE JobTypeID WHEN 1 THEN 0 WHEN 30 THEN 0 WHEN 234 THEN 0 ELSE 1 end)
    FROM BATCH.dbo.JOB (NOLOCK) WHERE StatusID = 3 
        AND JobTypeID NOT IN (1,30,4005)

INSERT INTO @JobInfo (JobType,JobID,StatusID,Message, DateCreated,ITEMS)
SELECT
0 as Jobtype,
'IMPORT' AS JobID,
CASE WHEN Count(CASE JobTypeID WHEN 191 THEN 0 WHEN 124 THEN 0 WHEN 4005 THEN 0 ELSE 1 end) >0 THEN N'AMBER' ELSE N'GREEN' END,
'Jobs running longer than 1 hour (ITEMS)',
CAST( @DateCreated AS NVARCHAR(30)),
COUNT(CASE JobTypeID WHEN 191 THEN 0 WHEN 124 THEN 0 WHEN 4005 THEN 0 ELSE 1 end)
    FROM IMPORT.dbo.JOB (NOLOCK) WHERE StatusID = 3 
        AND JobTypeID NOT IN (191,124,4005)
SELECT * FROM @JobInfo

然而，目前两者StatusID都是 AMBER，ITEMS分别是 2 和 4

SELECT * FROM BATCH.dbo.JOB (NOLOCK) WHERE StatusID = 3 --shows 4 rows, 30,1,1072,234
SELECT * FROM IMPORT.dbo.JOB (NOLOCK) WHERE StatusID = 3 --shows 3 rows, 4005,124,191

有人可以帮忙解释为什么会这样吗？

标签： sql