Query linking pairs preList: Difference between revisions

(One intermediate revision by the same user not shown)

Line 3:

== SQL ==

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 ==

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. [[User:Ttenbergen|Ttenbergen]] 10:30, 2021 July 28 (CDT)

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. [[User:Ttenbergen|Ttenbergen]] 10:30, 2021 July 28 (CDT)

== Related articles ==

@@ Line 3: / Line 3: @@
 == SQL ==
 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 ==
--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. [[User:Ttenbergen|Ttenbergen]] 10:30, 2021 July 28 (CDT)
+-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. [[User:Ttenbergen|Ttenbergen]] 10:30, 2021 July 28 (CDT)
 == Related articles ==