SQLTeam.com | Weblogs | Forums

Value present with specific value exclude from output

sql2008

#1

Hello Techie,

May i get some help for below scenario.

DECLARE @TABLE1 TABLE
(
SKU VARCHAR (50),
VCODE VARCHAR (50),
rollover VARCHAR (50)
)

INSERT @TABLE1

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

*/

Thanks,


#2

Something like this:

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)

#3

Thank You Very Much Gbritton... :smile:


#4

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)