CODEWITHSUNDEEP
sqlsql-server-2012
rickythefox
rickythefox

Reputation: 6851

Finding stores with non-zero stock

Given the table below and a list of ItemIDs, how do I find all StoreIDs where StockQty > 0 for all items in the list?

In the example below, given ItemID 5 and 6 I want the result to be 1. ItemID 5 would result in 1 and 2.

StoreID | ItemID | StockQty
---------------------------
 1      | 5      |  3       
 1      | 6      |  3       
 2      | 5      |  3       
 2      | 6      |  0       
 3      | 5      |  0       
 3      | 3      |  3

Upvotes: 0

Views: 28

Answers (2)

Esteban P.
Esteban P.

Reputation: 2809

I would solve it like this:

The important part is the HAVING. It's basically just counting ItemIDs where the StockQty is 0.

Logic:
If StockQty of the item is > 0, i "count" it as 0, else as 1.
If SUM of those values is 0, all ItemIDs have a StockQty bigger than 0.

    SELECT StoreID
      FROM yourtable
     WHERE ItemID IN (5,6) -- your list of Items
  GROUP BY StoreID
    HAVING SUM(CASE WHEN StockQty > 0 THEN 0
                    ELSE 1
                END
               ) = 0

Upvotes: 2

Serg
Serg

Reputation: 22811

For a table of items 

 declare @List table(item int);
 insert  @List(item)
 values (5),(6);

 SELECT StoreID, Count(*)
 FROM yourtable t
 JOIN @List l ON t.ItemID = l.item AND StockQty > 0
 GROUP BY StoreID
 HAVING Count(*) = (SELECT count(*) FROM @List)

Upvotes: 2

Related Questions