-- 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?
4条答案
按热度按时间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.
Now this results (from the sample data)
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...
Resulting from your sample data
So, lets pull together
At least if I am reading your question correctly.
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:
fiddle
t3irkdon3#
For your test you had no null values SO I set those up for a test as described.
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:
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:
Will get you records where the status is 'New' for the date '2023-01-01', now you need to handle the rest.
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.