Tuesday, December 2, 2014

SQL View: Show NULL until all "orders" have the same "orderstate"


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