SELECT '140053', 'M11', 'STONE' UNION ALL
SELECT '140053', 'M11', 'REC' UNION ALL
SELECT 'TCI950600','T16', 'REC' UNION ALL
SELECT 'PSR1400600303','P2J','STONE' UNION ALL
SELECT 'BUS14007', 'L22', 'NIT' UNION ALL
SELECT 'BUS14007', 'L22' , 'STONE' UNION ALL
SELECT 'TLS1400E', 'TSP' , 'RECFIT' UNION ALL
SELECT 'BOR140120','B25' , 'STONE' UNION ALL
SELECT 'BOR140120' ,'B25' , 'REC'
SELECT * FROM @TABLE1
/*
If same SKU Present with ROLLOVER 'STONE' and REC both,
or if SKU present only with ROLLOVER 'REC' then exclude from output
*/
-- OUTPUT
/*
SKU ,VCODE, rollover
PSR1400600303, P2J , STONE
BUS14007, L22, NIT
BUS14007, L22, STONE
TLS1400E, TSP, RECFIT
with cte as
(
select *
, COUNT(case when rollover = 'STONE' then 1 end) over(Partition by sku) as stonecount
, COUNT(case when rollover = 'REC' then 1 end) over(Partition by sku) as reccount
, COUNT(case when rollover not in ('REC', 'STONE') then 1 end) over(Partition by sku) as othercount
from @table1
)
select * from cte
where not (reccount > 0 and stonecount + othercount = 0)
I would like to use SUM over COUNT to avoid unnecessary NULL warnings
with cte as
(
select *
, SUM(case when rollover = 'STONE' then 1 else 0 end)
over(Partition by sku) as stonecount
, SUM(case when rollover = 'REC' then 1 else 0 end)
over(Partition by sku) as reccount
, SUM(case when rollover not in ('REC', 'STONE') then 1 else 0 end)
over(Partition by sku) as othercount
from @table1
)
select * from cte
where not (reccount > 0 and stonecount + othercount = 0)