DECLARE @orders TABLE (orderID INT, orderState BIT)
INSERT INTO @orders (orderID, orderState)
VALUES (1, NULL),(1, 0),(1, 1),(2, 1),(2, 1),(3, 0),(3, 1)
SELECT o1.orderID, MAX(CASE WHEN o2.orderID IS NOT NULL THEN CAST(o1.orderState AS INT) ELSE NULL END) AS orderState
FROM @orders o1
LEFT OUTER JOIN (SELECT orderID, MIN(CAST(orderState AS INT)) AS min, MAX(CAST(orderState AS INT)) AS max FROM @orders GROUP BY orderID) o2
ON o1.orderID = o2.orderID
AND o1.orderState = o2.min
AND o1.orderState = o2.max
GROUP BY o1.orderID
Try this.
No comments:
Post a Comment