Difference between revisions of "Query linking pairs preList"

From CCMDB Wiki
Jump to navigation Jump to search
 
Line 3: Line 3:
== SQL ==
== SQL ==
See [[CFE]] for current version, but here is an instance:  
See [[CFE]] for current version, but here is an instance:  
  SELECT L_PHI.D_ID, L_PHI_moreThanOne.PHIN, created_AdmitDtTm.Admit_DtTm
  SELECT L_PHI.PHIN,  
  FROM ((
        created_AdmitDtTm.Admit_DtTm,  
  SELECT L_PHI.PHIN, Count(L_PHI.D_ID) AS CountOfD_ID
        L_PHI.D_ID,
  FROM L_PHI
        linking_pairs_LatestLinkedPHINAdmit.D_ID
  GROUP BY L_PHI.PHIN
FROM (linking_pairs_multis 
  HAVING (((Count(L_PHI.D_ID))>1))
        INNER JOIN ((L_PHI  
  ORDER BY Count(L_PHI.D_ID)AS L_PHI_moreThanOne
                    INNER JOIN created_AdmitDtTm 
INNER JOIN L_PHI ON L_PHI_moreThanOne.PHIN = L_PHI.PHIN) INNER JOIN created_AdmitDtTm ON L_PHI.D_ID = created_AdmitDtTm.D_ID;
                        ON L_PHI.D_ID = created_AdmitDtTm.D_ID)
        LEFT JOIN linking_pairs_LatestLinkedPHINAdmit 
            ON L_PHI.D_ID = linking_pairs_LatestLinkedPHINAdmit.D_ID)   
            ON linking_pairs_multis.PHIN = L_PHI.PHIN)
        INNER JOIN linking_pairs_unlinkeds 
            ON L_PHI.D_ID = linking_pairs_unlinkeds.D_ID
WHERE (((linking_pairs_LatestLinkedPHINAdmit.D_ID) Is Not Null)) 
        OR (((linking_pairs_unlinkeds.D_ID) Is Not Null))
ORDER BY L_PHI.PHIN, created_AdmitDtTm.Admit_DtTm;


== Log ==
== Log ==

Latest revision as of 09:31, 2021 August 4

Query linking_pairs_preList is a query in CFE that is used by Populate linking pairs for making linking_pairs table.

SQL

See CFE for current version, but here is an instance:

SELECT L_PHI.PHIN, 
       created_AdmitDtTm.Admit_DtTm,  
       L_PHI.D_ID,  
       linking_pairs_LatestLinkedPHINAdmit.D_ID
FROM (linking_pairs_multis  
       INNER JOIN ((L_PHI 
                    INNER JOIN created_AdmitDtTm  
                        ON L_PHI.D_ID = created_AdmitDtTm.D_ID)  
       LEFT JOIN linking_pairs_LatestLinkedPHINAdmit  
           ON L_PHI.D_ID = linking_pairs_LatestLinkedPHINAdmit.D_ID)  
           ON linking_pairs_multis.PHIN = L_PHI.PHIN)  
       INNER JOIN linking_pairs_unlinkeds  
           ON L_PHI.D_ID = linking_pairs_unlinkeds.D_ID
WHERE (((linking_pairs_LatestLinkedPHINAdmit.D_ID) Is Not Null))  
       OR (((linking_pairs_unlinkeds.D_ID) Is Not Null))
ORDER BY L_PHI.PHIN, created_AdmitDtTm.Admit_DtTm;

Log

2021-07-28 - created query to replace hard coded SQL in VBA, both to fix linking pairs that took way longer in Access 2019 than it had in 2010, and to start using Service tmp entry dates rather than old Arrive DtTm field, when available. Ttenbergen 10:30, 2021 July 28 (CDT)

Related articles

Related articles: