Skip to content
Snippets Groups Projects

Use CoveredLivesPopulation as PatientMGBPatients

Merged Use CoveredLivesPopulation as PatientMGBPatients
hh_mgbpcp_vbc into main
Merged Hubbell, Harrison Taylor requested to merge hh_mgbpcp_vbc into main
Compare
and
2 files
+ 20
− 184
Compare changes
  • Side-by-side
  • Inline
Files
2
patient_mgb_patients/patient_mgb_patients.sql
+ 10
− 152
@@ -9,156 +9,14 @@
create view EAViewLibrary.PatientMGBPatients as
with pcmh_practice as (
select
prv.NPI,
prc.PracticeNM
from Payer.Provider.PCMHProviderPractice prv
inner join Payer.Provider.PCMHPractice prc on prv.PracticeID = prc.PracticeID
where (prv.PCMHTerminationDTS is null
or prv.PCMHTerminationDTS >= current_timestamp)
-- Do not include '%INELIGIBLE%' practices
and prv.PracticeID not in (549, 550, 750, 800, 850, 900, 997, 998)
), all_source_patients as (
select
ptn.EDWPatientID,
ptn.PatientID,
'Epic' as SourceDSC,
1 as EpicSourceFLG,
1 as PreferenceNBR,
null as PlanCD,
ipr.ProviderNPI as NPI,
ipr.ReportGroupID as ReportGroupID,
ipr.ReportGroupCD as ReportGroupCD,
coalesce(ipr.PracticeNM, pcmh.PracticeNM) as PracticeNM
from Epic.Patient.Patient_Enterprise ptn
inner join Epic.Patient.Patient4_Enterprise ptn4 on ptn.PatientID = ptn4.PatientID
inner join Epic.Patient.ValidPatient_Enterprise vp on ptn.PatientID = vp.PatientID
inner join Integration.MasterReference.ProviderRoster ipr on ptn.CurrentPCPProviderID = ipr.EpicProviderID
left join Integration.MasterReference.Patient mrp on ptn.EDWPatientID = mrp.EDWPatientID
left join Payer.ACODM.BeneficiaryDemographicDimension bdd on ptn.EDWPatientID = bdd.EDWPatientID
left join pcmh_practice pcmh on ipr.ProviderNPI = pcmh.NPI
where ptn.EDWPatientID is not null
and ptn.TestFLG = 0
and vp.IsValidPatientFLG = 'Y'
and coalesce(bdd.DeceasedFLG, 'N') != 'Y'
and coalesce(mrp.DeathFLG, 'N') != 'Y'
and ptn4.LivingStatusCD = 1
and mrp.DeathDTS is null
and ptn.DeathDTS is null
union all
select
bdd.EDWPatientID,
ptn.PatientID,
'Payer',
0,
2,
'PACO' as PlanCD,
pcpd.PCPNPI as NPI,
mmdf.PCPReportGroupID as ReportGroupID,
rg.ReportGroupCD,
coalesce(pcmh.PracticeNM, pcpd.PracticeNM) as PracticeNM
from Payer.ACODM.BeneficiaryDemographicDimension bdd
inner join Payer.ACODM.MemberMonthDetailFact mmdf on bdd.BeneficiaryDemographicID = mmdf.BeneficiaryDemographicID
inner join Payer.ACODM.PCPDimension pcpd on mmdf.PCPID = pcpd.PCPID
inner join Payer.ACODM.EligibilityDateDimension edd on mmdf.EligibilityDateID = edd.EligibilityDateID
inner join (
select max(edd.EligibilityDTS) as EligibilityDTS
from Payer.ACODM.MemberMonthDetailFact mmdf
inner join Payer.ACODM.EligibilityDateDimension edd on mmdf.EligibilityDateID = edd.EligibilityDateID
) mce on edd.EligibilityDTS = mce.EligibilityDTS
/* NOTE: Use new MBI identifier */
inner join Payer.ACODM.BeneficiaryProfileDimension bpd on bdd.CurrentMedicareBeneficiaryID = bpd.CurrentMedicareBeneficiaryID
and year(edd.EligibilityDTS) = year(bpd.ProfileEndDTS)
left join Payer.Reference.ReportGroup rg on mmdf.PCPReportGroupID = rg.ReportGroupID
left join Epic.Patient.Patient_Enterprise ptn on bdd.EDWPatientID = ptn.EDWPatientID
left join Integration.MasterReference.Patient mrp on bdd.EDWPatientID = mrp.EDWPatientID
left join pcmh_practice pcmh on pcpd.PCPNPI = pcmh.NPI
where bdd.EDWPatientID is not null
and bpd.ExcludeFLG = 'N'
and coalesce(bdd.DeceasedFLG, 'N') != 'Y'
and coalesce(mrp.DeathFLG, 'N') != 'Y'
and mrp.DeathDTS is null
and ptn.DeathDTS is null
union all
select
mdd.EDWPatientID,
ptn.PatientID,
'Payer',
0,
iif(pd.PlanCD = 'MHACO', 4, 3),
pd.PlanCD,
pcpd.PCPNPI as NPI,
mmdf.PCPReportGroupID as ReportGroupID,
rg.ReportGroupCD,
coalesce(pcmh.PracticeNM, pcpd.PracticeNM) as PracticeNM
from Payer.HealthPlanDM.MemberDemographicDimension mdd
inner join Payer.HealthPlanDM.MemberMonthDetailFact mmdf on mdd.MemberDemographicID = mmdf.MemberDemographicID
inner join Payer.HealthPlanDM.PCPDimension pcpd on mmdf.PCPID = pcpd.PCPID
inner join Payer.HealthPlanDM.EligibilityDateDimension edd on mmdf.EligibilityDateID = edd.EligibilityDateID
inner join Payer.HealthPlanDM.ProductDimension pd on mmdf.ProductID = pd.ProductID
inner join (
select
pd.PlanCD,
pd.ProductTypeCD,
pd.InsuranceTypeCD,
max(edd.EligibilityDTS) as EligibilityDTS
from Payer.HealthPlanDM.MemberMonthDetailFact mmdf
inner join Payer.HealthPlanDM.EligibilityDateDimension edd on mmdf.EligibilityDateID = edd.EligibilityDateID
inner join Payer.HealthPlanDM.ProductDimension pd on mmdf.ProductID = pd.ProductID
group by pd.PlanCD, pd.ProductTypeCD, pd.InsuranceTypeCD
) mce on edd.EligibilityDTS = mce.EligibilityDTS
and pd.PlanCD = mce.PlanCD
and pd.ProductTypeCD = mce.ProductTypeCD
and pd.InsuranceTypeCD = mce.InsuranceTypeCD
left join Payer.Reference.ReportGroup rg on mmdf.PCPReportGroupID = rg.ReportGroupID
left join Epic.Patient.Patient_Enterprise ptn on mdd.EDWPatientID = ptn.EDWPatientID
left join Integration.MasterReference.Patient mrp on mdd.EDWPatientID = mrp.EDWPatientID
left join Payer.ACODM.BeneficiaryDemographicDimension bdd on mdd.EDWPatientID = bdd.EDWPatientID
left join pcmh_practice pcmh on pcpd.PCPNPI = pcmh.NPI
where mdd.EDWPatientID is not null
and mmdf.PCPReportGroupID != 'XX'
and coalesce(bdd.DeceasedFLG, 'N') != 'Y'
and coalesce(mrp.DeathFLG, 'N') != 'Y'
and mrp.DeathDTS is null
and ptn.DeathDTS is null
)
select
asp.EDWPatientID,
asp.PatientID,
asp.NPI,
asp.ReportGroupID,
asp.ReportGroupCD,
asp.PracticeNM,
coalesce(pe.EpicActivelyManagedFLG, 0) as EpicActivelyManagedFLG,
asp.SourceDSC,
asp.EpicSourceFLG
from (
-- NOTE: Jill's original logic selects Epic over Payer, and medicare over
-- commercial. If there are any duplicates after that level, `PlanCD` is
-- selected in effectively alphabetical order. This occurs if a patient
-- has eligibility with multiple payers in the same member month.
-- Reproduce this deduplication idea here. If there are any duplicates
-- after that level, `ReportGroupID` is selected in ascending order. This
-- occurs if a patient has multiple eligibility records with the same payer
-- in the same month. Furthermore, if there are any more duplicates, `NPI`
-- is selected in ascending order. This also occurs if a patient has
-- multiple eligibility records with the same payer in the same month.
select *, row_number() over (partition by EDWPatientID order by PreferenceNBR, PlanCD, ReportGroupID, NPI) as RowNBR
from all_source_patients
) asp
left join (
select distinct
PatientID,
1 as EpicActivelyManagedFLG
from Epic.Encounter.PatientEncounter_Enterprise
where AppointmentStatusCD in (1, 2, 6)
and ContactDTS between dateadd(MONTH, -36, current_timestamp) and dateadd(MONTH, 6, current_timestamp)
and EncounterTypeCD in ('3', '50', '62', '69', '72', '76', '101', '111',
'150', '202', '210', '350', '1000', '1001', '1003', '1200', '1201', '1214',
'2101', '2501', '2502', '2522', '4000', '7781', '21001', '21002', '252201',
'210999001', '210999002', '210999904')
) pe on asp.PatientID = pe.PatientID
where asp.RowNBR = 1;
clp.EDWPatientID,
clp.PatientID,
clp.NPI,
rg.ReportGroupID,
clp.ReportGroupCD,
clp.PracticeNM,
clp.EpicActivelyManagedFLG,
clp.PatientSourceDSC as SourceDSC
from SAM.ValueBasedCare.CoveredLivesPopulation_Enterprise clp
left join Payer.Reference.ReportGroup rg on clp.ReportGroupCD = rg.ReportGroupCD