Query NDC Bad Postal Code

Data Integrity Checks
Summary:	Cross check of postal codes against known postal codes
Related:	Postal Code field
Firmness:	hard check
Timing:	always
App:	Centralized data front end.accdb
Coding:	query NDC_Bad_Postal_Code
Uses L Problem table:	No
Status:	implemented
Implementation Date:	2014-09-24
Backlogged:	No

We cross-check the postal codes we collect as part of Postal Code field collection against a list of known-good postal codes. Also see query NDC_Postal_Code_over_time.

CFE Data Integrity Checks Steps

Comparisons are done as part of vetting via NDC query NDC_Bad_Postal_Code in CFE. Steps will be:

if the patient has Province = Manitoba or CF :
1. check the postal code in EPR, correct if different than ours
2. if same as ours, check the address in EPR against Canada Post Postal Codes;
  - update patient's postal code if it was wrong or
  - make #update to Postal_Code_Master table if postal code was correct and just not in our list
if the patient is from any other province:
- if the Postal Code's first letter is not for the province where the patient lives you need to check EPR or internet to put the correct Postal code
- If the first letter is appropriate for province but triggers because it's not in the master list, then register the D_ID with Known data errors with query name "NDC_Bad_Postal_Code"
if the patient has Province = (US or NK or OS) there should be no postal code so query should not trigger at all

Open Postal_Code_Master table
update/add code
- you only need to fill in the fields PCode, rhaname, RHA_Acronym and town, we will eliminate the other fields.

2021-01-27 - changed query to only look at the last year of data to make it faster; any older records were already fixed
2016-08-end - cleaning started
2014-09-24 - implemented