SQL SELECT * WHERE value NOT IN list and IN another list

Question

I want to select a SKU that is not in ('DC01','5000'), but is in ('1003','1039','1012') where the SUM is greater than 3. I intend to get back zero records, as the SKU '000000001041106003' is in '1003' with StockOnHand greater than 3, but has a StoreID of 'DC01', however the SKU value of '000000001041106003' is returned. That SKU has a StoreID of 'DC01' and '1003'.

What do I need to do in order to get the desired outcome?

productName                    SKU  StoreId StockOnHand webenabled

.Speedo Yarn    000000001041106001  1003        1         1
.Speedo Yarn    000000001041106002  1003        3         1
.Speedo Yarn    000000001041106003  1003        4         1
.Speedo Yarn    000000001041106003  DC01        0         1


SELECT DISTINCT(SKU)
FROM etlStoreAssortment 
WHERE StoreId NOT IN ('DC01','5000') 
AND StoreId IN ('1003','1039','1012') GROUP BY SKU HAVING SUM(StockOnHand) > 3

Answer 1

WHERE looks at a single record. There is one record for '000000001041106003' where StoreId NOT IN ('DC01','5000') and StoreId IN ('1003','1039','1012'). What you are looking for though is where at least one record per group has or doesn't have a certain value. Use HAVING for this:

SELECT DISTINCT(SKU)
FROM etlStoreAssortment 
GROUP BY SKU 
HAVING SUM(StockOnHand) > 3
AND MAX(CASE WHEN StoreId IN ('DC01','5000') THEN 1 ELSE 0 END) = 0
AND MAX(CASE WHEN StoreId IN ('1003','1039','1012') THEN 1 ELSE 0 END) = 1;