0

I have an automated check script for each morning where the user will be informed of the current number of jobs running as long as they don't fall into the category of "expected running jobs".

I would expected StatusID to be GREEN for IMPORT and AMBER for BATCH due to the current counts being 4 and 3 respectivley

My current code is

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

However currently the StatusID is AMBER for both and the ITEMS are 2 and 4 respectively

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

Could someone please help explain why this is the case?

Badja
  • 857
  • 1
  • 8
  • 33
  • 2
    You should use `SUM` instead of `COUNT`. `COUNT` will add +1 even when your `CASE` [return 1 or 0](https://stackoverflow.com/questions/69534908/sql-case-when-result-outputting-conflicting-results/69535058?noredirect=1#comment122905776_69535058). – Pham X. Bach Oct 12 '21 at 09:43
  • 1
    See the above comment. But, you could use `COUNT` if you replace the `0` in your logic with `NULL`, which `COUNT` will ignore. Example: `COUNT(CASE JobTypeID WHEN 1 THEN 0 WHEN 30 THEN 0 WHEN 234 THEN 0 ELSE 1 end)` could be changed to: `COUNT(CASE JobTypeID WHEN 1 THEN null WHEN 30 THEN null WHEN 234 THEN null ELSE 1 end)` or `COUNT(CASE WHEN JobTypeID IN (1, 30, 234) THEN NULL ELSE 1 end)`, etc. – Jon Armstrong Oct 12 '21 at 09:45
  • If `JobTypeID` is guaranteed to be `NOT NULL`, this can be simplified even more. – Jon Armstrong Oct 12 '21 at 09:51
  • @JonArmstrong this worked perfectly. Would it count as NULL if only the 3 jobs excluded are running? – Badja Oct 12 '21 at 09:57
  • 1
    Most databases will evaluate as a count of 0, just like this one: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=68a1594cd2b338a0c88a15229662f711 – Jon Armstrong Oct 12 '21 at 10:01

0 Answers0