SQL Server Case Expression in Group Clause - ELSE "Don't Group"

cvxl0en2  于 2023-03-07  发布在  其他
关注(0)|答案(4)|浏览(151)
-- create a table
CREATE TABLE Jobs 
(
    JobNo int,
    Entered DATE,
    Linked int,
    Status varchar(255)
);

-- insert some values
INSERT INTO Jobs 
VALUES 
  (12345,'20211229',11135,'New'),
  (12345,'20211230',11135,'Cancelled'),
  (12346,'20221230',NULL,'New'),
  (12346,'20230101',NULL,'Cancelled'),
  (12345,'20230101',11135,'New'),
  (12346,'20230101',NULL,'New'),
  (12350,'20230101','12258','Billed'),
  (12351,'20230101',NULL,'New'),
  (12351,'20230101',NULL,'Cancelled'),
  (12351,'20230101',NULL,'New'),
  (12352,'20230102',NULL,'New'),
  (12353,'20230103','12249','New');

-- Test
SELECT JobNo 
FROM Jobs 
WHERE Status = 'New' AND Entered = '20230101' 
GROUP BY 
    CASE 
        WHEN Linked IS NULL 
            THEN JobNo 
            ELSE NULL 
    END;

-- Returns 12345 and I want 12345,12351 (2 total)

I want to return from the table:

  • Jobs 'Entered' on a specific date, with 'Status' = 'New'
  • Don't include if job has 'New' status on a previous date, unless it has a 'Linked' value

Is this the right approach to the problem?

mcdcgff0

mcdcgff01#

So, what you need is a LEFT JOIN to the secondary criteria vs the first.

Start your query to get distinct NEW jobs on the date in question.

SELECT distinct
      j.JobNo 
   FROM 
      Jobs j
   WHERE 
          j.Status = 'New' 
      AND j.Entered = '2023-01-01'

Now this results (from the sample data)

JobNo
12345  (you want)
12346  (you want excluded)
12351  (you want)

Netxt, a list of all jobs with NEW status PRIOR to the date in question. While at it, get a status of any "linked" entries such as...

select
      j.JobNo,
      max( case when j.Linked > 0 then 1 else 0 end ) HasAnyLinked
   from
      jobs j
   where
          j.entered < '2023-01-01'
      AND j.Status = 'New'
   group by
      j.JobNo

Resulting from your sample data

JobNo   HasAnyLinked
12345   1
12346   0

So, lets pull together

with WhatYouWant as
( the first query
),
LinkedJobs as
( the second query )

select
      w.*
   from
      WhatYouWant w
         LEFT JOIN LinkedJobs lj
            on w.JobNo = lj.JobNo
   where
         -- no prior "new" job entries
         lj.JobNo is null
         -- there WERE prior entries, but it HAS a link id
      OR lj.HasAnyLink = 1

At least if I am reading your question correctly.

cngwdvgl

cngwdvgl2#

Your linked has no NULL Values instead it uses empty strings, if your original data has NULL you need to change the last WHERE criteria:

SELECT DISTINCT JobNo FROM Jobs 
WHERE Status = 'New' AND Entered = '2023-01-01' AND Linked = ''
JobNo
12346
12351

fiddle

t3irkdon

t3irkdon3#

For your test you had no null values SO I set those up for a test as described.

;WITH jobs AS (
    SELECT * 
    FROM ( VALUES
     (12345,'2021-12-29',11135,'New'),
     (12345,'2021-12-30',11135,'Cancelled'),
     (12346,'2022-12-30',NULL,'New'),
     (12346,'2023-01-01',NULL,'Cancelled'),
     (12345,'2023-01-01',11135,'New'),
     (12346,'2023-01-01',NULL,'New'),
     (12350,'2023-01-01','12258','Billed'),
     (12351,'2023-01-01',NULL,'New'),
     (12351,'2023-01-01',NULL,'Cancelled'),
     (12351,'2023-01-01',NULL,'New'),
     (12352,'2023-01-02',NULL,'New'),
     (12353,'2023-01-03','12249','New')
     ) AS j ( JobNo ,  Entered ,  Linked , Status)
 ) 

SELECT
    JobNo 
FROM Jobs 
WHERE Status = 'New' 
    AND Entered = '2023-01-01'
    AND Linked IS NULL
GROUP BY JobNo;

Now for this specific case you want to GROUP BY JobNo which is OK however since that uses ALL the columns in the SELECT it is effectively the same as DISTINCT.

Here is another test showing the same results returned:

;WITH jobs AS (
    SELECT * 
    FROM ( VALUES
     (12345,'2021-12-29',11135,'New'),
     (12345,'2021-12-30',11135,'Cancelled'),
     (12346,'2022-12-30',NULL,'New'),
     (12346,'2023-01-01',NULL,'Cancelled'),
     (12345,'2023-01-01',11135,'New'),
     (12346,'2023-01-01',NULL,'New'),
     (12350,'2023-01-01','12258','Billed'),
     (12351,'2023-01-01',NULL,'New'),
     (12351,'2023-01-01',NULL,'Cancelled'),
     (12351,'2023-01-01',NULL,'New'),
     (12352,'2023-01-02',NULL,'New'),
     (12353,'2023-01-03','12249','New')
     ) AS j ( JobNo ,  Entered ,  Linked , Status)
 ) 

SELECT DISTINCT
    JobNo 
FROM Jobs 
WHERE Status = 'New' 
    AND Entered = '2023-01-01'
    AND Linked IS NULL;
z0qdvdin

z0qdvdin4#

I think people are confused by your requirements. In particular:
Don't include if job has 'New' status on a previous date, unless it has a 'Linked' value

What you need is a NOT EXISTS clause.

So:

SELECT DISTINCT
    JobNo 
FROM Jobs 
WHERE Status = 'New' 
    AND Entered = '2023-01-01';

Will get you records where the status is 'New' for the date '2023-01-01', now you need to handle the rest.

SELECT DISTINCT JobNO
FROM Jobs A
Where Status = 'New'
AND Entered = '2023-01-01'
AND NOT EXISTS (SELECT 1
    FROM Jobs B
    Where A.JobNo = B.JobNo
    AND B.Entered < A.Entered
    AND B.Status = 'New'
    AND A.Linked = '')

So the Not Exists checks for a record with the same JobNo on an earlier date with status of 'New' when the original record has no Linked status and only selects the original record if it can't find one.

相关问题