PDA View Update

Summary

What do you think about making this change to the PDA view? I thought we might begin by pulling all completed PDAs and their corresponding data outright then joining to the communication data pulled by the original code. The new part doesn't include the restrictions made in the where clause of the original code but I can make those changes and resubmit if we want to keep it tight/consistent.

What is the expected behavior?

The view should include both PDAs with documented communication data and PDAs without documented communication data.

Justification for request

iCMP PDA reporting

Relevant Code, Supporting Documentation, Etc.

select distinct
           pda_dates.PatientID
         , pda_dates.PatientEncounterID
         , pda_dates.UserID
         , communication.UserNM
         , pda_dates.SmartDataElementVAL
         , pda_dates.AssessmentDTS
         , communication.CommunicationID
         , communication.CommunicationDTS
         , communication.CommunicationTypeDSC
         , communication.CallOutcomeDSC
         , communication.CommentTXT
         , communication.EncounterTypeDSC


  from 
  ( SELECT 
        B.SmartDataElementVAL
       ,A.PatientLinkID as PatientID
       ,A.CurrentValueDTS AS AssessmentDTS
       ,A.UserID
       ,C.PatientEncounterID
  FROM Epic.Encounter.SmartDataElementData_Enterprise A

  left join Epic.Encounter.SmartDataElementValue_Enterprise B
  on a.ConceptValueID= b.ConceptValueID

  left join epic.encounter.PatientEncounter_Enterprise C
  on a.CSNID= C.PatientEncounterID

  Where A.ConceptValueID = B.ConceptValueID and A.ElementID in ('EPIC#99982', 'PHS#12049')) as PDA_dates

  left join 
  
  (select
    pe.PatientID,
    pe.PatientEncounterID,
    com.CommunicationID,
    com.CommunicationTypeDSC,
    com.CommunicationLocalDTS as CommunicationDTS,
    com.UserID,
    emp.UserNM,
    com.CallOutcomeDSC,
    com.CommunicationCommentTXT as CommentTXT,
    pe.EncounterTypeDSC,
    sde_v.SmartDataElementVAL,
    sde_d.CurrentValueDTS as AssessmentDTS
from Epic.Encounter.PatientEncounter_Enterprise pe
    inner join UserWork.EAViewLibrary.ReferenceMapEncounterType rmet on pe.EncounterTypeCD = rmet.EncounterTypeCD
        and rmet.MappedEncounterTypeDSC = 'Outreach'
    inner join Epic.Encounter.ReasonForVisit_Enterprise rfv on pe.PatientEncounterID = rfv.PatientEncounterID
    inner join Epic.Patient.CommunicationReferencePatientEncounter_Enterprise crpe on pe.PatientEncounterID = crpe.PatientEncounterID
    inner join Epic.Clinical.Communication_Enterprise com on crpe.CommunicationID = com.CommunicationID
    inner join Epic.Patient.Patient_Enterprise ptn on pe.PatientID = ptn.PatientID
    left join Epic.Person.Employee_Enterprise emp on com.UserID = emp.UserID
    left join Epic.Encounter.SmartDataElementData_Enterprise sde_d on pe.PatientEncounterID = sde_d.CSNID
        and sde_d.ElementID in ('EPIC#99982', 'PHS#12049')
    left join Epic.Encounter.SmartDataElementValue_Enterprise sde_v on sde_d.ConceptValueID = sde_v.ConceptValueID
where crpe.RelationshipCategoryCD = '1' 
    and com.CallTypeCD = 2              
    and rfv.ReasonID = 1097) as communication             
    
 ON PDA_dates.PatientID= communication.PatientID
  and PDA_dates.UserID= communication.UserID
  and PDA_dates.AssessmentDTS= communication.AssessmentDTS
  and PDA_dates.PatientEncounterID= communication.PatientEncounterID