Thanks so much for your response. I got the first part to work and give me exactly what I needed.
from test t1
left join test t2
on t2.[type] = 'primary'
and t1.provid = t2.provid
where t1.[type] = 'secondary'
and t2.provid is null
However, when I apply this to my actual data I get zero rows. Which has been the case in every way I have tried this scenario.
Here is what I am trying to work with:
---NO SPECIALTY DECLARED--GET LIST OF PRIMARIES TO USE AS ELIMINATION--
from qnxt.dbo.provspecialty ps
join qnxt.dbo.provider p on ps.provid = p.provid
where spectype = 'PRIMARY'
THEN BASE TABLE:
a1.provid as "PCP Provid",
a1.affiliateid as "PCP Affiliate",
p2.fullname as Fullname2,
p2.provtype as Provtype2,
a1.payflag as Payflag1,
mp.Specialtycode as MP_SpecialtyCode,
z.description as 'MP Specialty Description',
PS_SpecialtyCode = '',
a2.provid as "Payto Provid",
p3.fullname as Fullname3,
p3.provtype as Provtype3,
a2.affiltype as Affiltype2,
a2.affiliateid as "Payto Affiliate",
p4.fullname as Fullname4,
p4.provtype as Provtype4,
a2.payflag as Payflag2,
a2.pcp as PCP2,
cast(mp.effdate as date) as PCPEffdate,
cast(mp.termdate as date) as PCPTermdate,
cast(ek.effdate as date) as EnrollEff,
cast(ek.termdate as date) as EnrollTerm,
Flag = ''
from qnxt.dbo.memberpcp mp
join qnxt.dbo.affiliation a1 (nolock) on mp.affiliationid = a1.affiliationid
join qnxt.dbo.affiliation a2 (nolock) on mp.paytoaffilid = a2.affiliationid
join qnxt.dbo.provider p1 (nolock) on a1.provid = p1.provid
join qnxt.dbo.provider p2 (nolock) on a1.affiliateid = p2.provid
join qnxt.dbo.provider p3 (nolock) on a2.provid = p3.provid
join qnxt.dbo.provider p4 (nolock) on a2.affiliateid = p4.provid
join qnxt.dbo.provspecialty y (nolock) on p1.provid = y.provid
join qnxt.dbo.specialty z (nolock) on mp.specialtycode = z.specialtycode
left join qnxt.dbo.affiliation_audit aa (nolock) on a1.affiliationid = aa.affiliationid
left join qnxt.dbo.provider pr (nolock) on aa.provid = pr.provid
left join qnxt.dbo.entity e (nolock) on p1.entityid =e.entid
join QNXT.[dbo].[enrollkeys] ek (nolock) on mp.enrollid = ek.enrollid
left join qnxt.dbo.contractinfo ci (nolock) on mp.paytoaffilid = ci.affiliationid and ek.programid = ci.programid
join QNXT.dbo.memberpcp x (nolock) on x.enrollid = ek.enrollid
where mp.termdate > '2017-01-01'
[MP Specialty Description],
PS.Specialtycode as PS_SpecialtyCode,
Flag = 'PCP with no primary specialty declared'
FROM #BASE b
JOIN QNXT.DBO.PROVSPECIALTY PS (nolock) ON PS.PROVID = B.[PCP PROVID]
The final table is #NoPrimary. This is part of 16 queries that are union-ed together to flag different scenarios. This particular segment is trying to figure out which PCPs do not have a Primary Specialty declared, and only have a Secondary Specialty declared.
If anyone has any other ways to work through this I would welcome any suggestions. I just can't figure out why this will not work on the test data and not the real data.
Thanks so much!!!