tsunami

select data_source, count(*) from bar_iplan group by data_source

with BI as (
    select  pcn = claim_group_fk,--patient_control_no,--
            ds = data_source
    from    BAR_IPlan
), PCNs as (
    select  pcn, 
            count = count(distinct ds)
    from    BI
    group by pcn
), 
D166 as (select pcn, count = count(*) from BI where ds = 166 group by pcn),
D167 as (select pcn, count = count(*) from BI where ds = 167 group by pcn),
D173 as (select pcn, count = count(*) from BI where ds = 173 group by pcn),
D192 as (select pcn, count = count(*) from BI where ds = 192 group by pcn)
delete  X
from    BAR_IPlan X
where   data_source not in (192, 173, 167)
    and claim_group_fk in (select pcn from D167)

--select    (select count(distinct pcn) from PCNs where pcn not in (select pcn from D166)),
--      (select count(distinct pcn) from PCNs where pcn not in (select pcn from D167)),
--      (select count(distinct pcn) from PCNs where pcn not in (select pcn from D173)),
--      (select count(distinct pcn) from PCNs where pcn not in (select pcn from D192))