create table hhs_combine_bsm_final as
when d1.phy_name eq d2.physician_name and d1.phy_name ne '' then 'Y'
end as flag
from HHS_combine_bsm d1 left outer join exclude_v1 d2 on d1.phy_name=d2.physician_name;
Say for the above code we get the data as below:
terrid phyname flag
903110 SAMY BUNN Y
903110 david Herr
903110 peter pan
903110 samuel colt
9031101 sarah Y
9031101 williams Y
How to delete or not display if there is only one physician per terrid whose flag is 'Y'.
for instance we dont want SAMY BUNN, in terrid 930110 as he is the only one physician in that terrid whose flag is 'Y'.
create table two as select b.* from hhs_combine_bsm_final b
where b.terrid in (select a.terrid from (select terrid, count(flg) as cf from hhs_combine_bsm_final
where flg = 'Y' group by terrid) a
where a.cf > 1);
create table two as select b.* from one b where b.terrid in
(select a.terrid from (select terrid, count(flag) as cf from one
where flag = 'Y' group by terrid) a where a.cf > 1 OR FLAG ne 'Y' );