Data Integrity Checks

The Critical Care and Medicine Database has many data integrity checks built into our data collection process. This page is an index to our documentation for those.

We try to run the checks as early in the collection process as possible:

1. CCMDB.accdb Data Integrity Checks - run in CCMDB.accdb and notify Data Collector during the collection process
2. CFE Data Integrity Checks - run in Centralized data front end.accdb as part of the Data Processor's job
3. SAS Data Integrity Checks - run in SAS as part of the Statistician's final clean up of the data before any reports or data requests are released

Documenting a new cross check

• search the wiki to see if this already exists
• if not, make a new page with a self-explanatory name that follows any conventions we might use
• add

  {{Data Integrity Check}}

  at the top of the page and save
• click the "edit with form" tab at the top of the page to edit the Data Integrity Checks with a helpful form

List of Data Integrity Checks (SMW)

Current number of Data Integrity Checks: 191

|?DICRelatedConcepts = Related concepts

	Summary	Status	App	Category
Query NDC VAP no TISS	If a patient has a Acquired Diagnosis Pneumonia, ventilator-associated (VAP) and no TISS28 items T18 - Invasive Mechanical Ventilation (TISS Item) (T18) then this is an error.	declined	Centralized data front end.accdb	Centralized data front end.accdb VAP - Ventilator Associated Pneumonia Legacy Content Data Processing Data Integrity Checks TISS28 check
Check CRF vs ARF across multiple encounters	If a patient had a comorbid of CRF in any previous admission then ARF can not be an admit or complication in subsequent admissions.	declined	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb ICD10 Dx check CCI Px check Data Integrity Checks
Query check ICD10 needs awaiting if TR Dt and Dispo Dt on diff days	Any patient with a with Transfer Ready DtTm and Dispo DtTm on different days has to have one of the "awaiting" codes and vice versa.	declined	CCMDB.accdb	Transfer Ready Awaiting/delayed transfer ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check ICD10 stroke	Stroke, NOS can not be coded with other stroke codes.	declined	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Can't check ICD10 ARF vs APACHE ARF	Can't have ESRD and acute renal failure	declined	CCMDB.accdb	ICD10 Dx check APACHE check Data Integrity Checks ICD10 CCMDB.accdb
Query NDC Dialysis TISS CCI	Critical care patients who have CCI entries for a type of dialysis must have a TISS28 entry for the same type of dialysis as well, and vice versa.	declined	CCMDB.accdb	CCI Px check Data Integrity Checks CCMDB.accdb TISS28 check
Check if awaiting code is primary dx then Transfer Ready DtTm must be equal to Arrive DtTm	If an ICD10 code in Category:Awaiting/delayed transfer is Primary Admit Diagnosis/Dx Primary then Transfer Ready DtTm must equal "Arrive DtTm"	declined	CCMDB.accdb	Transfer Ready Awaiting/delayed transfer ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check CCI TISS spont breath no tube	A patient with T19 - Spontaneous breathing via ETT without PEEP/CPAP, with or without supplemental O2 (TISS Item) must also have (T22 - ETT Present (TISS Item) or T23 - Trach Tube Present (TISS Item))	declined	CCMDB.accdb	Data Processing Data Integrity Checks CCMDB.accdb TISS28 check
Query check ICD10 Inf Potential Infection must have pathogen or alt	every dx in Category:Potential infection must have a dx in Category:Pathogens or Not an Infection (ICD10 pathogen alternative) as Combined ICD10 codes.	declined	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check TISS each day	There needs to be at least one TISS entry in CCI Picklist for each day for each ICU profile.	declined	CCMDB.accdb	CCI Px check Registry checks Data Integrity Checks CCMDB.accdb TISS28 timing checks TISS28 check
Query TISS Errors NrTISSDays NE LOS	Length of stay (LOS) for all ICU patients must be consistent with their number of TISS days, incl no TISS days at all (ie missing forms).	declined	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb Legacy TISS28 check
Query TISS Errors CAM positive vs Dx	Checks that each T9 - CAM positive (TISS Item) goes with an ICD10 code from Category:Delirium	declined	CCMDB.accdb	ICD10 Dx check CCI Px check Data Integrity Checks CCMDB.accdb TISS28 check
Check VAP acquired only first encounter	VAP should only be Acquired Diagnosis on the first encounter.	declined	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb VAP - Ventilator Associated Pneumonia ICD10 Dx check Old dx check Registry checks Data Integrity Checks
Check pre acute consistent	consistency of Pre acute living situation; Dispo; Postal Code and Previous Location	declined	CCMDB.accdb	Postal Codes Pre-acute living situation Admit/Discharge Data Integrity Checks Registry Data CCMDB.accdb
Query check ICD10 duplicates	Admit and Comorbid ICD10 Diagnoses (except for Category:Pathogens or Category:Antibiotic resistance and Category:Mechanism and Colonized with organism (not infected)) can't be entered in duplicate.	declined	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Check APACHE physiological variable high low	an APACHE physiological variable is extremely high or low	implemented	CCMDB.accdb	APACHE check Data Integrity Checks APACHE II Physiological Variables CCMDB.accdb
Query check minimal data set incomplete	makes sure the Minimal Data Set is collected before records can be sent.	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Function Validate Previous Location	validates Previous Location field: Can not be blank function previous_NE_Location() - Previous Location field can't be same as Service/Location field	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Query check prev pre-admit consistent	for inpatient transfers within the WRHA, Previous Location field and Pre-admit Inpatient Institution field should be same.	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Query s tmp project item consistent	Checks that tmp project entries only contain combinations of project and item that are allowed.	implemented	CCMDB.accdb	Tmp check Data Integrity Checks TmpV2 CCMDB.accdb
Query s tmp apostrophe in comment	There must not be apostrophes in the comment field as they would mess with Julie's data analysis.	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Function Validate PHIN	for patients with Province = MB, the PHIN has to be (> 100 000 000 and <= 130 000 000) or 999 999 999 (to accommodate PHIN_field#Location_Manitoba_and_no_PHIN for out-of-province patients, the PHIN must be entered as blank checks Function PHIN same as Chart	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Function PHIN same as Chart	PHIN can't be the same as Chart	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Function Validate Province	Validates the Province field: can't be left blank must not fail function Validate_PHIN	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Date of Birth	The minimum age allowed is 10, the maximum age allowed is 120.	implemented	CCMDB.accdb	L Log table L PHI table Registry checks Data structure Data Collection Guide Data Integrity Checks Registry Data Demographics CCMDB.accdb CCMDB.accdb cross checks triggering for correct values Cognos Report Integrator
Function Validate Dispo	doesn't allow empty	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Query check dx primary not exactly one	There has to be exactly one dx per patient with Dx Primary checked, and it has to be an Admit Diagnosis	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check dx primary lowest priority	The dx with Dx Primary checked should be one of the group of Combined ICD10 codes with the lowest Dx Priority.	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check ICD10 date	Acquired Diagnosis / Complication must have Dx Date or Dx Date unknown, other Dx Type must have neither, and must be Admit DtTm<Dx_Date<Dispo_DtTm	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Query check ICD10 unfilled	ICD10 entry with missing Dx_Type, Dx_Priority or No Dx Entered	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Query check CCI no admin entries	There must be no No procedure entered/Blank procedure entry in CCI Picklist or CCI Component.	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb
Query check CCI Date	Px Date must be sensible when compared to first Service tmp entry and Dispo_DtTm	implemented	CCMDB.accdb	CCI Px check Data Integrity Checks CCMDB.accdb
Query check CCI Component unfilled	CCI Component entry with missing px, Px_Type or Px_Count	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb
Query check CCI Picklist unfilled	CCI Picklist entry with missing px, Px_Type or Px_Count	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb
Query check CCI component 1 and 2 compatible	Component CCIs must have compatible component 1 and component 2	implemented	CCMDB.accdb	CCI Px check Data Integrity Checks CCMDB.accdb
Check Function Validate PostalCode	Validates that Postal Code field only contains characters in format allowed for a postal code	implemented	CCMDB.accdb	Postal Codes Registry checks Data Integrity Checks CCMDB.accdb
Check function Validate Last name	can't be blank can't start with a space can't start with a hyphen only allows letters, spaces and hyphens	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Check function Validate First name	can't be blank can't start with a space can't start with a hyphen only allows letters, spaces and hyphens	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Check function location NE dispo	Service/Location field can't be same as Dispo field	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Check function previous NE Location	Previous Location field can't be same as Service/Location field	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Check Sub PharmCheck	Every CC record has to have at least one pharmacy record (possibly all zeros), and the number of drug days can't be GT LOS,	implemented	CCMDB.accdb	Data Integrity Checks Length of stay CCMDB.accdb
Check duplicate patient	there are no duplicate entries for the same patient ward admission on the laptop at send time	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
PL Diff Phin SameLN FN DOB	Checks that there are no likely matches of this patient with a different PHIN (ie typos)	implemented	Centralized data front end.accdb	Centralized data front end.accdb PL queries Registry checks Data Processing Data Integrity Checks
PL SameCHART Site Diff PHIN	A record with this Chart number already exists for this service/Location and it has a different PHIN.	implemented	Centralized data front end.accdb	Centralized data front end.accdb PL queries Registry checks Data Processing Data Integrity Checks
PL SamePHIN Site Diff chart	Another record exists for this site with the same PHIN and a different Chart number.	implemented	Centralized data front end.accdb	Centralized data front end.accdb PL queries Registry checks Data Processing Data Integrity Checks
Link suspect negative transit time query	Used in Correcting suspect links to find records where an earlier record for a patient with that PHIN has a Dispo DtTm that is later than the next record's Admit DtTm.	implemented	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb Registry checks Data Processing Data Integrity Checks Multiple Encounter linking
Link suspect dead then alive query	Used in Correcting suspect links to find records where an earlier record with that PHIN was listed as dispo field deceased, but a later entry then shows up.	implemented	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb Registry checks Data Processing Data Integrity Checks Multiple Encounter linking
Link suspect mismatch from ours incomplete query	Used in Correcting suspect links to find records where a later record with that PHIN has a Previous Location entry corresponding to one of our sites as per the s_dispo table.real_collection table, but an earlier corresponding entry doesn't exist for that location.	implemented	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb Registry checks Data Processing Data Integrity Checks Multiple Encounter linking
Link suspect mismatch to ours incomplete query	Used in Correcting suspect links to find records where an earlier record with that PHIN has a dispo entry corresponding to one of our sites as per the s_dispo table.real_collection, but a later corresponding entry doesn't exist for that location.	implemented	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb Registry checks Data Processing Data Integrity Checks Multiple Encounter linking
Check function Validate Pre Acute Living Situation	can't be blank	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Link suspect not same visitAdmitDtTm query	Checks for records that are likely part of the same admission but have different Visit Admit DtTm field.	implemented	Centralized data front end.accdb	Centralized data front end.accdb Registry checks Data Integrity Checks
Query check cooling vs arrest	Due to our definition of Targeted Temperature Management (TTM) that code can only be used if there is a Cardiac arrest dx	implemented	CCMDB.accdb	CCI Data Integrity Checks ICD10 CCMDB.accdb
Query check hernia type vs complication	any abdominal hernia complication dx must have an abdominal hernia type dx coded.	implemented	CCMDB.accdb	Hernia ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check AIDS vs HIV	A record can not have both AIDS (disease due to HIV) and HIV infection, asymptomatic	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query s tmp QASeptic tmp no dx	CC pt has QA Septic Shock tmp entry and no Septic Shock/Shock, septic dx	implemented	CCMDB.accdb	ICD10 Dx check Old dx check Tmp check Data Integrity Checks CCMDB.accdb
PL Chart 9 Digit	Finds any chart numbers that have >8 digits. This would usually mean someone confused a PHIN and a Chart number	implemented	Centralized data front end.accdb	Centralized data front end.accdb PL queries Registry checks Data Processing Data Integrity Checks
Query check ICD10 Comorbids NoComo code but others	If No Comorbidities (ICD10 code) is entered, there must really be no other comorbidities coded	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Query check ICD10 Comorbids Dx present	All complete patients must have at least one ICD10 entry for Dx Type Comorbid Diagnosis, which might be No Comorbidities (ICD10 code).	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Check Inf Antibiotic resistance must have pathogen or Infection with implied pathogen	every dx in Category:Antibiotic resistance must have a dx in Category:Infection with implied pathogen or in Category:Pathogens as Combined ICD10 codes.	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Check Inf Infection with implied pathogen must not have a pathogen combined code	every dx in Category:Infection with implied pathogen must not have a dx in Category:Pathogens as Combined ICD10 codes.	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Function Validate Chart	validates the Chart number: Chart must be numerical chart must be checks Function PHIN same as Chart no further checks at this time because chart numbers are different at different hospitals	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Sub Dx Checker	Runs most Category:Old_dx_check, Category:ICD10 Dx check, Category:CCI Px check, and a few other checks for dxs	implemented	CCMDB.accdb	ICD10 Dx check Old dx check CCI Px check Data Integrity Checks CCMDB.accdb
Query check ICD10 ESRD vs kidney transplant	If there is a Past history, transplanted kidney then there can only be Chronic kidney disease (end-stage renal/kidney disease, ESRD), Stage 5, GFR LT 15 if there is also Kidney transplant, failure or rejection or unspecified complication	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Function Validate Pre admit Inpatient Institution	validates Pre-admit Inpatient Institution field: Can not be blank Check function preAdmit NE Location	implemented	CCMDB.accdb	Data Integrity Checks CCMDB.accdb
Query check CCI Picklist duplicates	No duplication of CCI Picklist Px_Type, Px_Date, Px_Count	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb
Query check CCI Component duplicates	No duplication of CCI Component Px_Type, Px_Date	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb
Query s tmp QASeptic Dx no tmp ICD10	CC patient has Septic Shock/Shock, septic dx and no QA Septic Shock tmp entry	implemented	CCMDB.accdb	ICD10 Dx check Old dx check Tmp check Data Integrity Checks CCMDB.accdb
Query s tmp comfort care gt one	Only one entry allowed per patient.	implemented	CCMDB.accdb	Data Integrity Checks CCMDB.accdb
Query s tmp ABO TEE checks	Each of the three ABO TEE project entries must have one entry on the ITEM for patients with RecordStatus complete	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb Service/Location check
Link suspect mismatch pre inpt ours incomplete query	Checks for patients where Pre-admit Inpatient Institution is one of ours but we don't have a corresponding record.	implemented	Centralized data front end.accdb	Centralized data front end.accdb Registry checks Data Integrity Checks
Check function preAdmit NE Location	Pre-admit Inpatient Institution field can't be same as Service/Location field	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Query s tmp IICU consult dispo to IICU but no tmp	A patient is discharged to IICU and does not have an IICU consult entry.	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query NDC Bad Postal Code	Cross check of postal codes against known postal codes	implemented	Centralized data front end.accdb	Postal Codes Centralized data front end.accdb Registry checks Questions Data Processing Data Integrity Checks To do
Query send check centralized is owner	Run automatically at the beginning of sending to ensure that no records that are already vetted or sent can be re-sent	implemented	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Query check ICD10 mechanism vs trauma	Traumas need mechanisms, and mechanisms need traumas (see also Template:ICD10 Guideline Trauma w mechanism).	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check ICD10 Inf Infection req Pathogen must have one	every dx in Category:Infection requiring pathogen must have a dx in Category:Pathogens or Category:Double duty pathogen as Combined ICD10 codes.	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check ICD10 only 1 stage of renal failure	can only have one stage of renal failure in Comorbid Diagnosis and in Admit Diagnosis	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Query check ICD10 chronic vent acute resp failure	Chronic dependence on mechanical ventilator should not be coded with Respiratory failure (insufficiency) NOS, acute	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check CCI from OR no CCI	If from OR or PACU, must have at least 1 CCI Procedure other than No procedure performed	implemented	CCMDB.accdb	CCI Px check Registry checks Data Integrity Checks CCMDB.accdb
Query NDC Postal Code over time	Cross check of postal codes for consistency during a continuous hospital admission	implemented	Centralized data front end.accdb	Postal Codes Centralized data front end.accdb Registry checks Data Processing Data Integrity Checks
Query check tmp generate allowed	check that tmp project/item combo exists for this location for a project	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query s tmp Boarding Loc date item	Validates entries for Boarding Loc	implemented	CCMDB.accdb	Tmp check Registry checks Data Integrity Checks CCMDB.accdb
PL SamePhin Diff DOB Sex	Another record exists with the same PHIN but a different Date of Birth, Sex	implemented	Centralized data front end.accdb	Centralized data front end.accdb PL queries Registry checks Data Processing Data Integrity Checks
Query check tmp service or location duplicate	Service tmp entry and Boarding_Loc entries must not have duplicate dates and services resp. locations and comments	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query check tmp service and program consistency	Service tmp entry values must be consistent with the Program	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query check tmp ER Boarding Loc should exist if from ER	For patients with Previous Location ER at same site there should be a Boarding Loc ER.	implemented	CCMDB.accdb	Tmp check Questions Data Integrity Checks CCMDB.accdb
Query check CCI if no then none	If No procedure performed in CCI Picklist there must be no other entries in CCI Picklist or CCI Component	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb
Query check CCI TISS NrDays GT LOS	There are more TISS Item Px_Dates than there are days between the first Service tmp entry and Dispo DtTm	implemented	CCMDB.accdb	CCI CCI Px check Registry checks Data Integrity Checks Length of stay CCMDB.accdb TISS28 timing checks TISS28 check
Query check CCI TISS NrDays LT LOS	There are more TISS Item Px_Dates than there are days between the first Service tmp entry and Dispo DtTm	implemented	CCMDB.accdb	CCI Px check Registry checks Data Integrity Checks Length of stay CCMDB.accdb TISS28 timing checks TISS28 check
Query check CCI TISS if no then none	If No TISS Item (TISS Item) in CCI Picklist there must be no other entry from TISS Item List in CCI Picklist, and vice versa	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb TISS28 check
Query check ICD10 dx implying death must have appropriate dispo	If dx is (Brain death;Medical Assistance In Dying;Sudden cardiac death (and died)) then Dispo field must be one of the Deceased patients options	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks End-of-life related data CCMDB.accdb
Query check CCI TISS Intubation vs Intubated	Any marked TISS Item in T28 - Planned extubation (TISS Item) or in T29 - Unplanned extubation (TISS Item) must have the T22 - ETT Present (TISS Item) on the same date.	implemented	CCMDB.accdb	Data Integrity Checks CCMDB.accdb TISS28 check
Query check CCI TISS Vented without mechanism	Any marked TISS Item T18 - Invasive Mechanical Ventilation (TISS Item) has to have (T22 - ETT Present (TISS Item) or T23 - Trach Tube Present (TISS Item)) for the same date.	implemented	CCMDB.accdb	Tracheostomy Data Integrity Checks CCMDB.accdb TISS28 check
Query check transfer DtTm during admission	Transfer Ready DtTm tmp entry should never be earlier than first Boarding Loc dttm or later than Dispo DtTm	implemented	CCMDB.accdb	Transfer Ready Data Integrity Checks CCMDB.accdb
Query check CCI TISS Pharm Vasoactive	Compare Vasoactive drug IV continuous-single (TISS Item)/T14 - Vasoactive drug IV continuous-multiple simultaneous (TISS Item) against Pharmacy entries for vasoactive drugs.	implemented	CCMDB.accdb	Data Integrity Checks CCMDB.accdb TISS28 check
Query s tmp check combined Boarding Loc and TransferReadyDtTm	Validates that there are no unmatched Transfer Ready DtTm tmp entry and Boarding Loc as per Template:Combined instructions for Transfer Ready DtTm and Boarding Loc.	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query s tmp check Boarding Loc and TransferReadyDtTm pairs	Validates that combinations of Transfer Ready DtTm tmp entry and Boarding Loc with the same integer always appear as pairs as per Template:Combined instructions for Transfer Ready DtTm and Boarding Loc.	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query check tmp Boarding Loc Service first same	Validates that the DtTm for the first Service tmp entry is the same as that for the first Boarding Loc entry	implemented	CCMDB.accdb	Tmp check Registry checks Data Integrity Checks CCMDB.accdb
Query check has transfer ready date or checkbox	Each Transfer Ready DtTm tmp entry has to have either a dttm, or its checkbox checked.	implemented	CCMDB.accdb	Data Integrity Checks PatientFollow Project CCMDB.accdb
Query Check Inf Pathogens must have Infection requiring pathogen or Potential Infection	Dxs in Category:Pathogens (but not in Category:Double duty pathogen) must have either a Category:Infection requiring pathogen or Category:Potential infection as Combined ICD10 codes.	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb
Query check ICD10 awaiting has Transfer Ready DtTm	Any patient who has code in Category:Awaiting/delayed transfer has to have a (Transfer Ready DtTm or Transfer Ready DtTm tmp entry) on a different day than their Dispo DtTm.	implemented	CCMDB.accdb	Transfer Ready ICD10 Dx check Registry checks Data Integrity Checks CCMDB.accdb
PL 2Phin Fake or Blank	Finds blank PHINs and those entered to meet function Validate_PHIN so Data Processor can apply PseudoPHIN via Generating PseudoPHINs.	implemented	Centralized data front end.accdb	Centralized data front end.accdb PL queries Registry checks Data Processing Data Integrity Checks
Query check eliminated ICD10	disallows any currently entered diagnosis that has been eliminated	implemented	CCMDB.accdb	ICD10 Dx check CCI Px check Data Integrity Checks CCMDB.accdb
Query check eliminated CCI Pick	disallows any currently entered diagnosis that has been eliminated	implemented	CCMDB.accdb	CCI Px check Data Integrity Checks CCMDB.accdb
Query check eliminated CCI Comp	disallows any currently entered diagnosis that has been eliminated	implemented	CCMDB.accdb	CCI Px check Data Integrity Checks CCMDB.accdb
Query check tmp 2 BL TR S same tm	There can't be two Transfer Ready DtTm or two Boarding Loc or two Service tmp entries with the same date and time	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query check tmp Service and Boarding Loc during admission timeframe	Service tmp entry and Boarding Loc date and time var need to be between Admit DtTm and Dispo DtTm.	implemented	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query check pt from ER	part of validating Boarding Loc	implemented	CCMDB.accdb	Data Integrity Checks CCMDB.accdb
Query check CCI ICD10 Dialysis no Dx	If a profile has one of the #Dialysis CCI Items there should also be one of the #Dialysis qualifying Dxs.	implemented	CCMDB.accdb	ICD10 Dx check Data Integrity Checks CCMDB.accdb TISS28 check Dialysis
Query check CCI must have entry	There must be at least 1 CCI entry in L_CCI_Picklist table (which might be No procedure performed)	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb
Query check CCI vs LOS count days component	Confirm that CCI Collection Mode or CCI Component CCI collect count days items don't have a count > LOS.	implemented	CCMDB.accdb	CCI Px check Data Integrity Checks Length of stay Labs CCMDB.accdb
Query check CCI vs LOS count days picklist	Confirm that CCI Collection Mode for CCI Picklist CCI collect count days items don't have a count > LOS.	implemented	CCMDB.accdb	CCI Px check Data Integrity Checks Length of stay Labs CCMDB.accdb
Query check CCI TISS discontinuous ETT	T22 - ETT Present (TISS Item) should start with an T40 - Insertion of ETT (TISS Item) and end with an T28 - Planned extubation (TISS Item) or T29 - Unplanned extubation (TISS Item).	implemented	CCMDB.accdb	Data Integrity Checks CCMDB.accdb TISS28 check
Query check CCI TISS matching BoardingLoc time	If the Px Date component of a CCI Picklist has a time, then there must be a corresponding Boarding Loc entry	implemented	CCMDB.accdb	CCI CCI Px check Data Integrity Checks CCMDB.accdb TISS28 timing checks TISS28 check
Link suspect mismatch pre inpt should be ours incomplete query	What is description	implemented	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb Registry checks Data Processing Data Integrity Checks Multiple Encounter linking
Reconnect check orphans query	Record meets following conditions RecordStatus field is incomplete the record doesn't have the same SentDtTm field as the newest SentDtTm for that Service/Location.	implemented	Centralized data front end.accdb	Centralized data front end.accdb PL queries Data Processing Data Integrity Checks
Reconnect check missing L Tables content query	Complete patients must have entries in all of L_ICD10 table L_CCI_Picklist table L_Labs_Flowsheet table L_Pharm_Flowsheet table	implemented	Centralized data front end.accdb	Centralized data front end.accdb Data Integrity Checks
Reconnect check Completes query	Shows any records in that have RecordStatus "Complete", indicating failed send.	implemented	Centralized data front end.accdb	Centralized data front end.accdb CCI wiki infrastructure Data Integrity Checks
Query s tmp Boarding Loc ER delay reasonable	Flags Boarding Loc entries for ERs where a second Boarding Loc entry exists that is less than 30 min later or more than 67 hours later for CC or more than 73 hrs later for Med	implemented	CCMDB.accdb	Tmp check Registry checks Data Integrity Checks CCMDB.accdb
Query NDC dx implying death across encounters	Category:Diagnosis implying death across encounters	implemented	Centralized data front end.accdb	Multiple encounter longitudinal consistency check Centralized data front end.accdb ICD10 Dx check Registry checks Data Integrity Checks End-of-life related data
Query Check BL TR combiner null	Checks that all Boarding Loc / Transfer Ready DtTm tmp entry pairs have a non-null combiner integer.	implemented	CCMDB.accdb	Transfer Ready Admit/Discharge Data Integrity Checks CCMDB.accdb
Query check long transfer delay	Is the Transfer Delay (Critical Care) or Transfer Delay (Medicine) unreasonably long?	needs review	CCMDB.accdb	Transfer Ready Questions Admit/Discharge Data Integrity Checks CCMDB.accdb To do
Controlling Dx Type for ICD10 codes	Some dxs can't be Dx Types Primary Admit Diagnosis, Comorbid Diagnosis, Admit Diagnosis or Acquired Diagnosis	needs review	CCMDB.accdb	ICD10 Dx check Questions Data Integrity Checks ICD10 CCMDB.accdb To do
Query Import request matcher	Records in for which we have patients in L_Log but no lab records from DSM	needs review	DSM Labs Consistency check.accdb	DSM Labs Extract Data Integrity Checks DSM Labs Consistency check.accdb DSM check
Function long LOS()	LOS/Length of Stay should not be unlikely long based on historical LOS for a given ward (Service/Location field).	needs review	CCMDB.accdb	Registry checks Data Integrity Checks Length of stay 2016 Time and Place changes CCMDB.accdb Service/Location check
Query check no consecutive same BL or Service	Check that there are no two identical Boarding Locs or Services without a different one with a time stamp in between those two.	needs review	CCMDB.accdb	Data Integrity Checks CCMDB.accdb To do
Check organ donors must be dead	organ donors must be deceased	not feasible	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Query s tmp Borrow no borrow on own unit	Can't have Project Borrow arrive on own unit	not feasible	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query check ICD10 ESRD vs ARF	Can't have ESRD and acute renal failure, except if there was a transplant during this ward stay	not feasible	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Query check ICD10 ESRD and AKI only if transplant	A patient who has an ESRD dx and an AKI dx is an error unless they also have a kidney transplant CCI.	ready to implement	CCMDB.accdb	ICD10 Dx check Data Integrity Checks ICD10 CCMDB.accdb
Query check ICD10 trach dxs consistent	Tracheostomy related CCI and ICD10 codes must be consistent with each other.	ready to implement	CCMDB.accdb	Tracheostomy ICD10 Dx check CCI Px check Data Integrity Checks CCMDB.accdb TISS28 check
Query check ICD10 CLI vs CCI CentralLine	Checks for patients who have a Iatrogenic, infection, central venous catheter-related bloodstream infection (CVC-BSI, CLI) but no T17 - Central venous catheter (TISS Item).	ready to implement	CCMDB.accdb	ICD10 Dx check Old dx check Data Integrity Checks CCMDB.accdb To do TISS28 check
Query check CCI TISS time means must be two	If the Px Date component of a CCI Picklist has a time, then there must be a corresponding Boarding Loc entry	ready to implement	CCMDB.accdb	CCI Px check Data Integrity Checks CCMDB.accdb To do TISS28 timing checks TISS28 check
Query check CCI CXR vs LOS	Confirm that a Category:Labs Imaging count is not unreasonably high	ready to implement	CCMDB.accdb	CCI Px check Data Integrity Checks Length of stay Labs CCMDB.accdb CCMDB.accdb cross checks triggering for correct values To do
Query check Transfer DtTm after paired BL and before later BL	For each pair, the Transfer Ready DtTm tmp entry should never be earlier any Boarding Loc dttm, nor later than the next Boarding Loc	ready to implement	CCMDB.accdb	Transfer Ready Data Integrity Checks CCMDB.accdb
Query check ICD10 trach create but no TISS	If there is an acquired Tracheostomy creation there also has to be a Trach Tube Present (TISS Item)	ready to implement	CCMDB.accdb	Tracheostomy CCI Px check Data Integrity Checks CCMDB.accdb TISS28 check
Query check ICD10 trach has trach but no TISS	If there is a Tracheostomy, has one (Z93.0) ICD10, there should likely be a T23 - Trach Tube Present (TISS Item)	ready to implement	CCMDB.accdb	Tracheostomy ICD10 Dx check CCI Px check Data Integrity Checks CCMDB.accdb TISS28 check
Check BRR/XBR vs cardiac arrest dx	If there is an Admit Diagnosis or Acquired Diagnosis of at least one of: Witnessed Cardiac Arrest Unwitnessed Cardiac Arrest then there has to be a BRR collection entry.	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Check Function Panelling admit transfer same	If patient has Panelling or Discharge Planning as Admit Diagnosis then Arrive DtTm field must be equal to Transfer Ready DtTm field.	retired	CCMDB.accdb	Legacy Content Old dx check Data Integrity Checks CCMDB.accdb
Query check ApARF CRF	ARF (APACHE) and CRF - Chronic Renal Failure can not be coded together for the same patient.	retired	CCMDB.accdb	Legacy Content ICD10 Dx check Data Collection Guide Data Integrity Checks CCMDB.accdb
Query Check Renal IncompatibleDxs	can't have ARF and CRF in same record	retired	CCMDB.accdb	Legacy Content Old dx check Data Integrity Checks CCMDB.accdb
Query check trach dx task	make sure Trach Task is entered correctly	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Query s tmp QAInf basic	checks that none of int, float, bool fields are used in this data.	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Query s tmp QAInf dx no tmp	If Complication Diagnosis is one of: Iatrogenic, infection, central venous catheter-related bloodstream infection (CVC-BSI, CLI) Pneumonia, ventilator-associated (VAP) then there has to be a L_TmpV2 entry with project "ICU Infection Audit"	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Query s tmp QAInf tmp no dx	If "QAInf" entry is present in L_TmpV2 then program must be "CC" the corresponding diagnosis must exist	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Query s tmp QAInf LT 48 hrs after admit	DtTm of QAInf entry has to be at least 48hrs after patient admission unless the inf_dttm is blank.	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Query s tmp ACP	error if ACP Status collection in ICU is missing part of the entry.	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb
Query check VAP admit must be from ICU	VAP should only be Admit Diagnosis when Previous Location is an ICU.	retired	CCMDB.accdb	Multiple encounter longitudinal consistency check VAP - Ventilator Associated Pneumonia ICD10 Dx check Old dx check Registry checks Data Integrity Checks CCMDB.accdb
Query NDC VAP unacceptable date	Checks for Arrive DtTm before QA Infection VAP date and Dispo DtTm after QA Infection VAP date.	retired	Centralized data front end.accdb	Centralized data front end.accdb Old dx check Registry checks Data Integrity Checks
Query NDC VAP AcqDX but NoVAP DateinTMPV2	Checks that any record with an Acquired Diagnosis VAP has a QA Infection VAP entry with a date.	retired	Centralized data front end.accdb	Centralized data front end.accdb Old dx check Registry checks Data Integrity Checks
Query NDC VAP No AcqDX but VAP DateinTMPV2	Checks that any record with QA Infection VAP entry with a date also has an Acquired Diagnosis VAP .	retired	Centralized data front end.accdb	Centralized data front end.accdb Old dx check Registry checks Data Integrity Checks
Check eliminated dx	disallows any currently entered diagnosis that has been eliminated	retired	CCMDB.accdb	Old dx check Data Integrity Checks CCMDB.accdb
Query NDC zCRRT TISS no tmp	Record has T33 - CRRT (TISS Item) but no CRRT Project tmp entry	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Data Integrity Checks Legacy Tmp check Legacy TISS28 check
Query NDC zCRRT tmp no TISS	Record has CRRT tmp but no T33 - CRRT (TISS Item) entry	retired	Centralized data front end.accdb	Centralized data front end.accdb Data Integrity Checks Legacy Tmp check Legacy TISS28 check
Query check CCI CRRT vs Tmp	If CCI CRRT (incl volume removal via PRISMA device) exists there should be a tmp CRRT entry and vice versa	retired	CCMDB.accdb	Tmp check CCI Px check Data Integrity Checks CCMDB.accdb
Query NDC CLI unacceptable date	Checks that the QA Infection CLI Dt is between the Arrive DtTm and Dispo DtTm	retired	Centralized data front end.accdb	Centralized data front end.accdb Old dx check Registry checks Data Integrity Checks
Query s tmp Borrow sensible DtTm vs no borrow	Checks that data for Project Borrow arrive follows that project's	retired	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query s tmp Borrow duplicates	Checks that data for Project Borrow arrive follows that project's	retired	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Query check overstay not entered	check that all has been entered for the Overstay project	retired	CCMDB.accdb	Tmp check Data Integrity Checks OverstayProject CCMDB.accdb
Query check overstay pt from our med	pts from a location where we collect should not have Overstay colour	retired	CCMDB.accdb	Tmp check Data Integrity Checks OverstayProject CCMDB.accdb
Query check overstay no colour	Med pts must have Overstay colours	retired	CCMDB.accdb	Tmp check Data Integrity Checks OverstayProject CCMDB.accdb
Query check dispo lower acuity than location	check for patients going to lower level of care (based on S dispo table.acuity_level) but without Transfer_Ready_DtTm; Check will launch an error but allow no date, ie it's just a reminder, see Level of care hierarchy.	retired	CCMDB.accdb	Transfer Ready Legacy Content Data Integrity Checks CCMDB.accdb
Query check ICD10 ESRD vs AP ARF	ARF (APACHE) and CRF - Chronic Renal Failure can not be coded together for the same patient.	retired	not entered	ICD10 Dx check Data Collection Guide Data Integrity Checks
Function Transfer Ready DtTm vs status	If Transfer_Ready_DtTm is blank, TR_info_status must not be; if TR_info_status has content, Transfer_Ready_DtTm must be blank.	retired	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Function Validate Transfer Ready DtTm	doesn't allow violation of function Transfer_Ready_DtTm_vs_status	retired	CCMDB.accdb	Registry checks Data Integrity Checks CCMDB.accdb
Query TISS Errors vent vs vent2300	Any marked TISS Item T - Invasive mechanical ventilation at 2300 hrs (TISS Item) has to have a corresponding marked TISS Item T18 - Invasive Mechanical Ventilation (TISS Item) for the same date.	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Data Integrity Checks Legacy Registry check Legacy TISS28 check
Query TISS Errors CVC vs CVC2300	Any marked TISS Item T - Central Venous Catheter at 2300 (TISS Item) has to have a corresponding marked TISS Item T17 - Central venous catheter (TISS Item) for the same date.	retired	TISS28.accdb	Data Integrity Checks TISS28.accdb Legacy TISS28 check
Query TISS Errors BIPAP vs BIPAP2300	Any marked TISS Item T - BIPAP at 2300 (TISS Item) has to have a corresponding marked TISS Item T21 - Non-invasive CPAP or BIPAP (TISS Item) for the same date.	retired	TISS28.accdb	Data Integrity Checks TISS28.accdb Legacy TISS28 check
Query TISS Errors D ID not in CFE	The D_ID on a TISS28 form does not exist in CFE	retired	TISS28.accdb	TISS28 Scanning Process Data Integrity Checks TISS28.accdb Legacy TISS28 check
Query NDC TISS CCI endoscopic	If pt has T26 & 27 - Endoscopy (TISS Item) then there must be an endoscopic CCI for that day.	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Data Integrity Checks Legacy CCI Px check Legacy TISS28 check
Query NDC zCRRT CCI Px but no TISS	If there is a T33 - CRRT (TISS Item) then there should be a CRRT (incl volume removal via PRISMA device) CCI Px	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Data Integrity Checks Legacy CCI Px check Legacy TISS28 check
Query NDC zCRRT TISS but no CCI Px	If there is a T33 - CRRT (TISS Item) then there should be a CRRT (incl volume removal via PRISMA device) CCI Px	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Data Integrity Checks Legacy CCI Px check Legacy TISS28 check
Query TISS Errors TISS date out of admission	TISS date is not during admission	retired	TISS28.accdb	Legacy Content TISS28 Scanning Process Data Integrity Checks TISS28.accdb Legacy Registry check Legacy TISS28 check
Query TISS Errors duplicate Items	A TISS28 item exists twice	retired	TISS28.accdb	Legacy Content TISS28 Scanning Process Data Integrity Checks TISS28.accdb Legacy TISS28 check
Link suspect transfer ready greater than dispo	Checks for records that have Transfer Ready DtTm field is greater than Dispo DtTm field.	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Registry checks Data Integrity Checks
Link suspect transfer ready before arrive date	Checks for records that have Transfer Ready DtTm field is before Arrive_DtTm field.	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Registry checks Data Integrity Checks
Query TISS Errors missing days	There are no TISS28 entries for a day that should have at least one based on Accept DtTm and Dispo DtTm	retired	TISS28.accdb	Legacy Content TISS28 Scanning Process Data Integrity Checks TISS28.accdb Legacy TISS28 check
Query check transfer ready tmp for each Boarding Loc	There should be a Transfer Ready DtTm tmp entry for each Boarding Loc entry of a profile	retired	CCMDB.accdb	Transfer Ready Data Integrity Checks CCMDB.accdb
Query NDC Trach Dx TISS	Tracheostomy ICD10s and CCIs must be consistent with Trach TISS.	retired	Centralized data front end.accdb	Tracheostomy TISS28 Legacy Centralized data front end.accdb Legacy Content ICD10 Dx check Data Integrity Checks Legacy CCI Px check
Query s tmp Boarding Loc no borrow but entry	Validates entries for Boarding Loc	retired	CCMDB.accdb	Tmp check Registry checks Data Integrity Checks CCMDB.accdb
Query check tmp Transfer Ready not before Boarding Loc	Validates that the DtTm for a Transfer_Ready_DtTm_tmp entry is not before its matching Boarding Loc entry	retired	CCMDB.accdb	Tmp check Registry checks Data Integrity Checks CCMDB.accdb
Query NDC Pacemaker acquired vs TISS	Records with Acquired Procedure of Pacemaker insertion, temporary that don't have date-matching Pacemaker insertion or vice versa.	retired	Centralized data front end.accdb	Centralized data front end.accdb Legacy Content Data Integrity Checks
Query NDC cardioversion dx vs TISS	If pt has Acquired Procedure Cardioversion (EXCLUDE defibrillation-we are not tracking), then the TISS28 item T26 & 27 - Cardioversion (TISS Item) (T41) must be marked.	retired	Centralized data front end.accdb	Centralized data front end.accdb CCI Px check Data Integrity Checks TISS28 check
Query check CCI InExtubation consistent	Any marked TISS Item T40 - Insertion of ETT (TISS Item) or in T22 - ETT Present (TISS Item) must have at least one marked TISS item either in T28 - Planned extubation (TISS Item) or in T29 - Unplanned extubation (TISS Item)	retired	Centralized data front end.accdb	Centralized data front end.accdb Data Integrity Checks
Query s tmp IICU consult DtTm	"IICU consult" must have either a date or the check box checked. If they have a date, it has to be between Arrive_DtTm and Dispo_DtTm.	retired	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Check Accept Loc Park	if Previous Location = <your hospital> ER (parked) and Pre-admit Inpatient Institution = "NA / not applicable" then there must be a Accept_Loc_park entry. if there is an Accept_Loc_park entry, then the Previous Location must be <your hospital> ER (parked) and Pre-admit Inpatient Institution must be "NA / not applicable"	retired	CCMDB.accdb	Legacy Content Tmp check Registry checks Data Integrity Checks CCMDB.accdb
Check AcceptDtTm entered or missing checked	Either Accept DtTm field must have content or Accept DtTm missing field must be checked.	retired	CCMDB.accdb	APACHE check Registry checks Data Integrity Checks CCMDB.accdb
Function Dispo Chronological()	Checks that the registry times are in a sensible chronological order.	retired	CCMDB.accdb	Legacy Content Registry checks Data Integrity Checks 2016 Time and Place changes CCMDB.accdb
Query s tmp check combined BL and TRDtTm nonsequential	Validates that the combiner integers for Transfer Ready DtTm tmp entry and Boarding Loc are consecutive with the date/time_vals	retired	CCMDB.accdb	Tmp check Data Integrity Checks CCMDB.accdb
Function Validate previous service	Validates Previous Service field: can't be empty/blank	retired	CCMDB.accdb	Legacy Content Data Integrity Checks CCMDB.accdb Legacy Registry check
Query check previous location vs service inconsistent	patients with Previous Location field in medicine must have Previous Service field medicine; ICU (except STB ACCU, STB CICU/ICCS) must have previous service "critical care".	retired	CCMDB.accdb	Data Integrity Checks CCMDB.accdb Legacy Registry check

Checks under review

See Data Integrity Check under review