March 15, 2019 at 12:35 pm
I have a weird one that is hard to explain.
So I am looking for any Providers that are not listed as a Primary. So I did a select PROVID from PROVSPECIALTY (#primary) table which gives me about 73000 rows of PROVIDs. Then I did a select PROVID from PROVSPECIALTY where SPECTYPE = 'SECONDARY' (#sec) which gives 3600 rows. Then I ran select distinct * from #sec where provid not in (select distinct * from #primary) which gives me what I want which is 192 rows.
What my question is is how can I join the 192 records to my base table to get all the fields needed for those provids?
I have tried every kind of join, where exists, etc.
This is the base table that gets all the fields:
select distinct
mp.Affiliationid,
a1.provid as "PCP Provid",
p1.Fullname,
p1.Provtype,
a1.Affiltype,
a1.affiliateid as "PCP Affiliate",
p2.fullname as Fullname2,
p2.provtype as Provtype2,
a1.payflag as Payflag1,
a1.PCP,
mp.Enrollid,
mp.Pcptype,
Spectype,
mp.Specialtycode,
z.description as 'Specialty Description',
mp.Paytoaffilid,
a2.provid as "Payto Provid",
p3.fullname as Fullname3,
p3.provtype as Provtype3,
a2.affiltype as Affiltype2,
a2.affiliateid as "Payto Affiliate",
a2.payflag,
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,
mp.Svczip,
mp.Createid,
mp.Createdate,
mp.Lastupdate,
mp.Updateid,
ci.ContractID,
cast(ek.effdate as date) as EnrollEff,
cast(ek.termdate as date) as EnrollTerm,
Flag = ''
into #BASE
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
left join qnxt.dbo.contractinfo ci (nolock) on mp.paytoaffilid = ci.affiliationid
join QNXT.[dbo].[enrollkeys] ek (nolock) on mp.enrollid = ek.enrollid
join QNXT.dbo.memberpcp x (nolock) on x.enrollid = ek.enrollid
where mp.termdate > '2017-01-01'
I wasn't sure what information to include in terms of data but if someone could give me a lead on how to solve this I will be happy to include test data. Thank you for any help!
March 15, 2019 at 1:05 pm
You need to be checking the affiliation effective dates on those tables to really get what you're looking for, and probably the affiltype of 'service' or 'group'. You also don't want to be using affiliation_audit at all, but if you do you definitely need to be looking at the changetype to make sure you're not including DELETEs, and only looking at either 'CHANGE BEFORE' or 'CHANGE AFTER', not both, in addition to your 'INSERT' changetypes.
Aside from that, you're looking for assigned PCPs who only have secondary specialties? Or you're looking for all providers who have only secondary specialties, regardless of whether they have members assigned?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 15, 2019 at 1:13 pm
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
left join qnxt.dbo.contractinfo ci (nolock) on mp.paytoaffilid = ci.affiliationid
join QNXT.[dbo].[enrollkeys] ek (nolock) on mp.enrollid = ek.enrollid
join QNXT.dbo.memberpcp x (nolock) on x.enrollid = ek.enrollid
where mp.termdate > '2017-01-01'
and not exists(select * from #primary p (nolock) where a1.provid = p.provid)
and not exists(select * from #primary p (nolock) where a1.affiliateid = p.provid)
and not exists(select * from #primary p (nolock) where a2.provid = p.provid)
and not exists(select * from #primary p (nolock) where a2.affiliateid = p.provid)
March 15, 2019 at 1:21 pm
just had a thought here, are you looking for "secondary PCP"? If so, that doesn't mean the PCP's specialty is marked secondary, it's actually the pcptype within the memberpcp table, where pcptype='SEC'
reference for what I was saying earlier about needing to check the dates on the affiliations:DECLARE @startDate date,@endDate date;
SET @startDate = GETDATE();
SET @endDate = '12/31/2078';
SELECT TOP 10 *
FROM dbo.enrollkeys ek
JOIN dbo.member m ON ek.memid=m.memid
JOIN dbo.memberpcp mp ON ek.enrollid = mp.enrollid
JOIN dbo.affiliation pa ON mp.affiliationid=pa.affiliationid
JOIN dbo.provider p ON pa.provid=p.provid
JOIN dbo.provider svc ON svc.provid=pa.affiliateid
JOIN dbo.affiliation paytoaffil ON mp.paytoaffilid = paytoaffil.affiliationid
JOIN dbo.provider payto ON payto.provid=paytoaffil.affiliateid
WHERE ek.termdate >= @StartDate
AND ek.effdate <= @EndDate
AND ek.segtype = 'int'
AND ek.effdate <= ek.termdate
AND mp.pcptype = 'SEC'
AND mp2.pcptype = 'SEC'
AND mp.affiliationid != mp2.affiliationid
AND mp.termdate >= @StartDate
AND mp.effdate <= @EndDate
AND pa.affiltype='service'
AND pa.termdate >= @StartDate
AND pa.effdate <= @EndDate
AND paytoaffil.affiltype = 'group'
AND paytoaffil.termdate >= @StartDate
AND paytoaffil.effdate <= @EndDate
This assumes that
1. someone has actually affiliated members correctly to the PCPs, the wizard on the front end doesn't let them know if they are accidentally affiliating the PCP to the affiltype='GROUP', so you probably need a separate check for ones that are incorrect (or relax that requirement in the above to find that out)
2. that your provider folks are affiliating providers correctly and have all those affiliations up to date. It's possible to have old affiliations here in memberpcp, only way to know is to relax the requirement for the dates above and see if it changes. If it is, that's a whole different data integrity report for the provider team to clean up and/or the PCP assignment team to clean up
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 15, 2019 at 1:37 pm
and I just pulled that code out of my toolbox, forgot to tie in the provspecialty, if you are truly looking for secondary provider specialties you'll need to check the dates of the provspecialty span against your date parameters also, just to be sure.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 15, 2019 at 3:25 pm
shel 29143 - Friday, March 15, 2019 12:35 PMI have a weird one that is hard to explain.
So I am looking for any Providers that are not listed as a Primary. So I did a select PROVID from PROVSPECIALTY (#primary) table which gives me about 73000 rows of PROVIDs. Then I did a select PROVID from PROVSPECIALTY where SPECTYPE = 'SECONDARY' (#sec) which gives 3600 rows. Then I ran select distinct * from #sec where provid not in (select distinct * from #primary) which gives me what I want which is 192 rows.
What my question is is how can I join the 192 records to my base table to get all the fields needed for those provids?
I think you are missing criteria from your first selection. A query with a WHERE clause is necessarily a subset of the same query without a WHERE clause. I think you meant select PROVID from PROVSPECIALTY WHERE SPECTYPE = 'PRIMARY'-- (#primary)
Try the following instead.
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY PROVID ORDER BY SPECTYPE) AS rn
FROM <your complex query>
WHERE SPECTYPE in ('PRIMARY', 'SECONDARY')
)
SELECT *
FROM CTE
WHERE rn = 1
AND SPECTYPE = 'SECONDARY'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 15, 2019 at 4:02 pm
drew.allen - Friday, March 15, 2019 3:25 PMshel 29143 - Friday, March 15, 2019 12:35 PMI have a weird one that is hard to explain.
So I am looking for any Providers that are not listed as a Primary. So I did a select PROVID from PROVSPECIALTY (#primary) table which gives me about 73000 rows of PROVIDs. Then I did a select PROVID from PROVSPECIALTY where SPECTYPE = 'SECONDARY' (#sec) which gives 3600 rows. Then I ran select distinct * from #sec where provid not in (select distinct * from #primary) which gives me what I want which is 192 rows.
What my question is is how can I join the 192 records to my base table to get all the fields needed for those provids?I think you are missing criteria from your first selection. A query with a WHERE clause is necessarily a subset of the same query without a WHERE clause. I think you meant
select PROVID from PROVSPECIALTY WHERE SPECTYPE = 'PRIMARY'-- (#primary)Try the following instead.
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY PROVID ORDER BY SPECTYPE) AS rn
FROM <your complex query>
WHERE SPECTYPE in ('PRIMARY', 'SECONDARY')
)
SELECT *
FROM CTE
WHERE rn = 1
AND SPECTYPE = 'SECONDARY'Drew
Drew, can you expand maybe on why the CTE method rather than the NOT EXISTS() that Jonathan provided? I'll go play around when I get a chance, but "ask smarter people than me" is always my first move
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 18, 2019 at 10:33 am
jonathan.crawford - Friday, March 15, 2019 4:02 PMdrew.allen - Friday, March 15, 2019 3:25 PMshel 29143 - Friday, March 15, 2019 12:35 PMI have a weird one that is hard to explain.
So I am looking for any Providers that are not listed as a Primary. So I did a select PROVID from PROVSPECIALTY (#primary) table which gives me about 73000 rows of PROVIDs. Then I did a select PROVID from PROVSPECIALTY where SPECTYPE = 'SECONDARY' (#sec) which gives 3600 rows. Then I ran select distinct * from #sec where provid not in (select distinct * from #primary) which gives me what I want which is 192 rows.
What my question is is how can I join the 192 records to my base table to get all the fields needed for those provids?I think you are missing criteria from your first selection. A query with a WHERE clause is necessarily a subset of the same query without a WHERE clause. I think you meant
select PROVID from PROVSPECIALTY WHERE SPECTYPE = 'PRIMARY'-- (#primary)Try the following instead.
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY PROVID ORDER BY SPECTYPE) AS rn
FROM <your complex query>
WHERE SPECTYPE in ('PRIMARY', 'SECONDARY')
)
SELECT *
FROM CTE
WHERE rn = 1
AND SPECTYPE = 'SECONDARY'Drew
Drew, can you expand maybe on why the CTE method rather than the NOT EXISTS() that Jonathan provided? I'll go play around when I get a chance, but "ask smarter people than me" is always my first move
The CTE method should only read the table once, whereas the NOT EXISTS() method reads/scans the table multiple times. One of the factors in improving performance is reducing the number of reads/scans.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply