Sub Query - return only all orders ready to pick

Question

Sub Query - return only all orders ready to pick

Bron Tamulis

Ten Centuries

Points: 1090
More actions
May 18, 2020 at 4:04 pm

#3752637

I have a table with some data, Having hard time returning values based upon two conditions.
Create Table X_ReadytoPick
(
SOPstatus varchar(50),
OpsStatus varchar(50),
OrderNumber varchar(50)
)
INSERT INTO X_ReadytoPick
VALUES
('Acknowledgement','Okay','ORD001'),
('Pack Complete','Picked','ORD002'),
('Pack Complete','Picked','ORD002'),
('Pack Complete','Picked','ORD002'),
('DocsPrinted','Okay','ORD003'),
('Acknowledgement','NotYet','ORD004'),
('Acknowledgement','Okay','ORD004'),
('Acknowledgement','NotYet','ORD004')
I want to return only two orders out of the dataset (8 records 4 orders) from above......
ORD001 and Ord003 are the only orders that all rows for the order='Okay' (OpsStatus) and SOPstatus is in ('Acknowledgement','DocsPrinted')
I know I have to create a subquery and grouping...……..
select SOPstatus, OpsStatus, SopNumber from X_ReadytoPick
where SOPstatus in ('Acknowledgement','DocsPrinted')
gets me Orders ORD001,ORD002, ORD004 - But I don't want ORD004 since only 1 of the three records for ORD004 are 'Okay'
Basically needing to identify all orders with all items 'okay' and ready to pick.....
Thank you in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246955 More actions · Answer 1

Like this?

DROP TABLE IF EXISTS #X_ReadytoPick;

CREATE TABLE #X_ReadytoPick
(
    SOPstatus VARCHAR(50)
   ,OpsStatus VARCHAR(50)
   ,OrderNumber VARCHAR(50)
);

INSERT #X_ReadytoPick
VALUES
('Acknowledgement', 'Okay', 'ORD001')
,('Pack Complete', 'Picked', 'ORD002')
,('Pack Complete', 'Picked', 'ORD002')
,('Pack Complete', 'Picked', 'ORD002')
,('DocsPrinted', 'Okay', 'ORD003')
,('Acknowledgement', 'NotYet', 'ORD004')
,('Acknowledgement', 'Okay', 'ORD004')
,('Acknowledgement', 'NotYet', 'ORD004');

SELECT *
FROM #X_ReadytoPick xrp
WHERE NOT EXISTS
(
    SELECT *
    FROM #X_ReadytoPick xrp2
    WHERE xrp.OrderNumber = xrp2.OrderNumber
          AND xrp2.OpsStatus <> 'Okay'
)
      AND xrp.SOPstatus IN ('Acknowledgement', 'DocsPrinted');

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101085 More actions · Answer 2

SELECT OrderNumber
FROM #X_ReadytoPick
GROUP BY OrderNumber
HAVING MAX(CASE WHEN OpsStatus = 'Okay' AND SOPstatus IN ('Acknowledgement', 'DocsPrinted') THEN 0 ELSE 1 END) = 0
ORDER BY OrderNumber /*optional, of course*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 3

Phil - your response worked beautifully for me. Thank you very much.

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 4

I will try this reply as well. Thank you so much for taking time to reply.

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 5

Scott - worked quite well as well. Nice to have two options to review and learn from. Your query requires grouping all the data elements but it's simplicity (even I understand it) it great.

operosewa Newbie Points: 1 More actions · Answer 6

Your Blog is very nice. Wish to see much more like this. Thanks for sharing your information