SQL Query using In and Not In clause on the same column

You should be able to use the following query:

select distinct t.[product name], 
  coalesce(p.QOH, 0) QOH
from yourtable t
left join
(
  select t1.[product name], count(*) QOH
  from yourtable t1
  where [Product Status] = 'Storage'
    and not exists (select [product id]
                    from yourtable t2
                    where [Product Status] = 'Delivered'
                      and t1.[product id] = t2.[product id])
  group by t1.[product name]
) p
  on t.[product name] = p.[product name]

See SQL Fiddle with Demo

The issue with your original query is that a product cannot have two statuses at the same time. You were attempting to return rows with both the status of Storage and Delivered and that is logically impossible.

I used a subquery that returns the rows with a status of Storage, but where the product id does not also have another row in the table with the status of Delivered (this is the not exists in the where clause).

Once you have those results, you have to join back to your table to return all distinct products.

This query gives the result:

| PRODUCT NAME | QOH |
----------------------
|            A |   1 |
|            B |   0 |
|            C |   0 |

I don't fully agree with the saying that this is illogical. IN and NOT IN clause on the same column is legit and logical. It's just like doing Set Minus (Set A - Set B).

That being said, the example above

SELECT Product Name, Count(Product Id) 
FROM table_t1 
WHERE Product Status IN ('Storage') AND Product Status NOT IN ('Delivered')

is the exact same as

SELECT Product Name, Count(Product Id) 
FROM table_t1 
WHERE Product Status IN ('Storage')

thus the result. Not very useful in this example. But there are occasions when it is useful to have IN and NOT IN being used together, even on the same column.

For example, I have a long whitelist of items I want to include in IN, and meanwhile, I want to filter out some items in an existing blacklist. Sure, we can do Set Minus first and let the query do IN (Result of whitelist - blacklist). But we may also do IN (whitelist) AND NOT IN (blacklist).

You're filtering the table by [Product Status] at the beginning, so you won't see B and C this way. You need to use grouping:

Select 
    [Product Name]
    ,QOH = sum(case when [Product Status]='Storage' then 1 else 0 end)
group by 
    [Product Name]

SELECT ProductName, count(distinct ProductID)
from(
SELECT ProductName, ProductID
FROM Table_1
GROUP BY ProductName, ProductID
HAVING MAX(ProductStatus)='Storage' 
and MIN(ProductStatus)<>'Delivered')
X
GROUP BY ProductName

I can suggest similar variant, without unnecessary joins and function calls -

DECLARE @temp TABLE
(
      ProductName CHAR(1)    
    , ProductID INT  
    , ProductStatus VARCHAR(10)
)

INSERT INTO @temp (ProductName, ProductID, ProductStatus)
VALUES 
    ('A', 1, 'Rollout'),
    ('A', 1, 'Storage'),
    ('A', 1, 'Delivered'),
    ('A', 5, 'Storage'),
    ('B', 2, 'Rollout'),
    ('C', 3, 'Rollout'),
    ('A', 4, 'Rollout'),
    ('A', 5, 'Rollout'),
    ('B', 6, 'Rollout'),
    ('C', 7, 'Rollout')

SELECT 
      t.ProductName 
    , [Count] = COUNT(1)
FROM @temp t
WHERE t.ProductStatus = 'Storage'
    AND NOT EXISTS ( 
        SELECT 1
        FROM @temp t2
        WHERE t2.ProductStatus = 'Delivered'
            AND t.ProductID = t2.ProductID 
    )
GROUP BY ALL t.ProductName