This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find order which step is 0 with status D and for the same order other status are P.
Input Data:
1 2 3 4 5 6 7 8 9 |
OrderID Step Status ------- ----------- ------ ABC 0 D ABC 1 P ABC 2 P ABC 3 P XYZ 0 D XYZ 1 D EFQ 0 D |
Expected Output:
1 2 3 |
OrderID ------- ABC |
Create a table with sample data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE tbl_Orders ( OrderID CHAR(5) ,Step INTEGER ,Status CHAR(1) ) GO INSERT INTO tbl_Orders VALUES ('ABC', 0, 'D'), ('ABC', 1, 'P'), ('ABC', 2, 'P'), ('ABC', 3, 'P'), ('XYZ', 0, 'D'), ('XYZ', 1, 'D'), ('EFQ', 0, 'D'), ('EFQ', 1, 'D') GO |
Solution 1: Using CTE
1 2 3 4 5 |
with ctetest as ( SELECT OrderID,Step,Status FROM tbl_Orders WHERE Step = 0 and Status = 'D' ) select distinct a.OrderID from ctetest a inner join tbl_Orders b on a.OrderID = b.OrderID and b.Status = 'P' |
Solution 2: Using GROUP BY .. Having
1 2 3 4 5 6 |
SELECT OrderID FROM tbl_Orders GROUP BY OrderID HAVING COUNT(*) = COUNT(CASE WHEN Step <> 0 AND Status = 'P' THEN 1 ELSE NULL END) + COUNT(CASE WHEN Step = 0 AND Status = 'D' THEN 1 ELSE NULL END) |
Please try the different solutions for this puzzle and share it via comment...