Query linking pairs preList: Difference between revisions

(3 intermediate revisions 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 ((SELECT~~ L_PHI.~~PHIN~~, ~~Count(L_PHI~~.D_ID~~) AS CountOfD_ID~~

created_AdmitDtTm.Admit_DtTm,

FROM L_PHI

L_PHI.D_ID,

~~GROUP BY~~ L_PHI.~~PHIN~~

linking_pairs_LatestLinkedPHINAdmit.D_ID

~~HAVING (((Count(L_PHI~~.D_ID)~~)>1))~~

FROM (linking_pairs_multis

~~ORDER BY Count(~~L_PHI.D_ID)) ~~AS L_PHI_moreThanOne 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~~;

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

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 ((SELECT L_PHI.PHIN, Count(L_PHI.D_ID) AS CountOfD_ID
+        created_AdmitDtTm.Admit_DtTm,
-FROM L_PHI
+        L_PHI.D_ID,
-GROUP BY L_PHI.PHIN
+        linking_pairs_LatestLinkedPHINAdmit.D_ID
-HAVING (((Count(L_PHI.D_ID))>1))
+ FROM (linking_pairs_multis
-ORDER BY Count(L_PHI.D_ID))  AS L_PHI_moreThanOne 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;
+        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 ==
--07-28 - created query to replace hard coded SQL in VBA
+-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 ==