Query check has transfer ready date or checkbox: Difference between revisions

← Older edit Newer edit →

@@ Line 17: / Line 17: @@
 ** checkbox is checked and comment is not either "not ready" or "not available"
 ** checkbox is not checked but there is a comment
+{{Discuss |
+*Currently only implemented without the comment cross checks because almost all collectors are not following the instructions.
+*I had a hunch that at least some collectors aren't entering the comment field as instructed and wanted to know how common a problem this cross check would find, so I tweaked it to run in CFE and checked (SQL at bottom). There were over 1000. People seem to be using the field to indicate which TRDT is for which [[Boarding Loc]]. Which is reasonable, because hard to keep track of otherwise, but it means I can't do that check for now.
+*'''Do we want to change the instructions to match what people are doing? Or change the cross check? '''
+}}
+{{Collapsable
+| always= SQL for full query in [[CFE]]
+| full=
+SELECT L_Log.D_ID, First_Boarding_Loc.dttm AS First_Boarding, L_TmpV2_Transfer_Ready_DtTm.dttm AS TRDtTm, L_TmpV2_Transfer_Ready_DtTm.Legit_No_TRDT, L_TmpV2_Transfer_Ready_DtTm.comment_var, L_Log.Dispo_DtTm, Not (Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],0)>0) And Not [legit_no_trdt] AS No_date_and_no_check, (([L_TmpV2_Transfer_Ready_DtTm].[dttm]>0)) And [legit_no_trdt] AS Date_and_check, [Legit_No_TRDT] And Nz([comment_var])="" AS Check_but_no_comment, Not [Legit_No_TRDT] And Nz([comment_var])>"" AS Comment_but_no_check, L_Log.RecordStatus
+FROM ((SELECT L_TmpV2.D_ID, Min([date_var]+[time_var]) AS dttm FROM L_TmpV2 WHERE (((L_TmpV2.Project)="Boarding Loc")) GROUP BY L_TmpV2.D_ID)  AS First_Boarding_Loc RIGHT JOIN L_Log ON First_Boarding_Loc.D_ID = L_Log.D_ID) INNER JOIN (SELECT L_TmpV2.D_ID, [time_var]+[date_var] AS dttm, L_TmpV2.bool_var AS Legit_No_TRDT, comment_var FROM L_TmpV2 WHERE (((L_TmpV2.Project)="Transfer Ready DtTm")))  AS L_TmpV2_Transfer_Ready_DtTm ON L_Log.D_ID = L_TmpV2_Transfer_Ready_DtTm.D_ID
+WHERE (((Not (Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],0)>0) And Not [legit_no_trdt])=True) AND ((L_Log.RecordStatus)<>"incomplete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True)) OR ((((([L_TmpV2_Transfer_Ready_DtTm].[dttm]>0)) And [legit_no_trdt])=True) AND ((L_Log.RecordStatus)<>"incomplete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True)) OR ((([Legit_No_TRDT] And Nz([comment_var])="")=True) AND ((L_Log.RecordStatus)<>"incomplete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True)) OR (((Not [Legit_No_TRDT] And Nz([comment_var])>"")=True) AND ((L_Log.RecordStatus)<>"incomplete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True))
+ORDER BY L_TmpV2_Transfer_Ready_DtTm.comment_var;
+}}
+{{Collapsable
+| always= SQL for full query in [[CCMDB.accdb]]
+| full=
+SELECT L_Log.Pat_ID, First_Boarding_Loc.dttm AS First_Boarding, L_TmpV2_Transfer_Ready_DtTm.dttm AS TRDtTm, L_TmpV2_Transfer_Ready_DtTm.Legit_No_TRDT, L_TmpV2_Transfer_Ready_DtTm.comment_var, L_Log.Dispo_DtTm, Not (Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],0)>0) And Not [legit_no_trdt] AS No_date_and_no_check, (([L_TmpV2_Transfer_Ready_DtTm].[dttm]>0)) And [legit_no_trdt] AS Date_and_check, [Legit_No_TRDT] And Nz([comment_var])="" AS Check_but_no_comment, Not [Legit_No_TRDT] And Nz([comment_var])>"" AS Comment_but_no_check, L_Log.RecordStatus
+FROM ((SELECT L_TmpV2.Pat_ID, Min([date_var]+[time_var]) AS dttm FROM L_TmpV2 WHERE (((L_TmpV2.Project)="Boarding Loc")) GROUP BY L_TmpV2.Pat_ID)  AS First_Boarding_Loc RIGHT JOIN L_Log ON First_Boarding_Loc.Pat_ID = L_Log.Pat_ID) INNER JOIN (SELECT L_TmpV2.Pat_ID, [time_var]+[date_var] AS dttm, L_TmpV2.bool_var AS Legit_No_TRDT, comment_var FROM L_TmpV2 WHERE (((L_TmpV2.Project)="Transfer Ready DtTm")))  AS L_TmpV2_Transfer_Ready_DtTm ON L_Log.Pat_ID = L_TmpV2_Transfer_Ready_DtTm.Pat_ID
+WHERE (((Not (Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],0)>0) And Not [legit_no_trdt])=True) AND ((L_Log.RecordStatus)="Complete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True)) OR ((((([L_TmpV2_Transfer_Ready_DtTm].[dttm]>0)) And [legit_no_trdt])=True) AND ((L_Log.RecordStatus)="Complete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True)) OR ((([Legit_No_TRDT] And Nz([comment_var])="")=True) AND ((L_Log.RecordStatus)="Complete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True)) OR (((Not [Legit_No_TRDT] And Nz([comment_var])>"")=True) AND ((L_Log.RecordStatus)="Complete") AND ((Nz([L_TmpV2_Transfer_Ready_DtTm].[dttm],Nz([first_boarding_loc].[dttm],#10/15/2020#))>=#10/15/2020#)=True));}}
 == Log ==