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

From CCMDB Wiki
Jump to navigation Jump to search
m (Ttenbergen moved page Check has transfer ready entry to Query check has transfer ready date or checkbox without leaving a redirect)
m (Decided at Task meeting that we will not need comments and therefore don't need to check for them.)
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{Data Integrity Check
{{Data Integrity Check
|DIC_summary=each record should have transfer ready entry and any entry needs to be internally consistent
|DIC_summary=Each [[Transfer Ready DtTm tmp entry]] has to have either a dttm, or its checkbox checked.
|DIC_related_concepts=Transfer Ready DtTm tmp entry
|DIC_related_concepts=Transfer Ready DtTm tmp entry
|DIC_coding=query check_has_transfer_ready_entry_or_checkbox
|DIC_timing=complete
|DIC_status=needs review
|DIC_coding=query check_has_transfer_ready_date_or_checkbox
|DIC_implementation_date=2021-02-03
|DIC_status=implemented
|DIC_app=CCMDB.accdb
|DIC_app=CCMDB.accdb
|DIC_backlogged=No
|DIC_backlogged=No
}}
}}


* make sure there either is an TR entry for each [[Level of care hierarchy]] component of the stay, or the "no entry" rules are followed correctly
The following will trigger an error:
* [[RecordStatus]] Complete and either TransferReady dttm or first [[Boarding Loc]] is after 2020-10-15 (ie. [[Change from Service Location to Service, Boarding Loc and Transfer Ready DtTm tmp entry]])
* and one of the following:
** checkbox is checked and yet a date is present
** checkbox is not checked, and no date is present


== Needs review ==
{{Discuss |
* On a first read I am not sure how we would make sure this exists for each level of care; it would be a complicated query; how badly do we want this?
* Is there a simplified version that only makes sure a first entry exists, and that any entry that does exist is internally consistent (no time checkbox vs having a time vs presence of acceptable comment entry)
}}


{{Data Integrity Check List}}
{{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 ==
* 2021-04-29 - discussed at task meeting that there need not be comments for Transfer Ready DtTm, and therefore no check required.
* 2021-02-03 - implemented


== Related articles ==  
== Related articles ==  

Latest revision as of 13:45, 2021 April 29

Data Integrity Checks
Summary: Each Transfer Ready DtTm tmp entry has to have either a dttm, or its checkbox checked.
Related: Transfer Ready DtTm tmp entry
Firmness: not entered
Timing: complete
App: CCMDB.accdb
Coding: query check_has_transfer_ready_date_or_checkbox
Uses L Problem table: not relevant for this app
Status: implemented
Implementation Date: 2021-02-03
Backlogged: No
  • Cargo


  • SMW


  • Categories: 
  • form:

The following will trigger an error:


SQL for full query in CFE   

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;

SQL for full query in CCMDB.accdb   

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

  • 2021-04-29 - discussed at task meeting that there need not be comments for Transfer Ready DtTm, and therefore no check required.
  • 2021-02-03 - implemented

Related articles

Related articles:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Query_check_has_transfer_ready_date_or_checkbox&oldid=150289"