Just realized I didn’t have the state in my match and was getting dups . there is 2 matches for the same address , city and zip but in different states :
[cid:[email protected]] can’t believe they would match a northern Milwaukee ZIP code to Illinois From: Tim Meyer <[email protected]> Date: Tuesday, August 1, 2017 at 2:10 PM To: "Kowalski, George" <[email protected]> Cc: "[email protected]" <[email protected]> Subject: Re: Geocoding data matchup ? George, This is the logic we use (Oracle) and it matches up the records pretty well: LEFT JOIN LZ.GEOCODED_ADDRESS ON NVL(GEOCODED_ADDRESS.ARC_ADDRESS,'null') = NVL(TRIM(UPPER(PATIENT.ADD_LINE_1)),'null') AND NVL(GEOCODED_ADDRESS.ARC_CITY,'null') = NVL(TRIM(UPPER(PATIENT.CITY)),'null') AND NVL(GEOCODED_ADDRESS.ARC_STATE,'null') = NVL(TRIM(UPPER(zc_state.abbr)),'null') AND NVL(GEOCODED_ADDRESS.ARC_ZIP,'null') = NVL(TRIM(UPPER(patient.zip)),'null') I think we also upper cased all our addresses when we send them to be geocoded, which is why we don't have any UPPERs on the left side. You could also replace the NVLs with actual NULL checks but that makes the query quite a bit larger. Tim Tim Meyer Manager - Research Data Management Academic Health Center - Office of Information Systems IT Manager, CTSI-BPIC Phone: 612.624.8386 [email protected]<mailto:[email protected]> On Tue, Aug 1, 2017 at 1:52 PM, Kowalski, George <[email protected]<mailto:[email protected]>> wrote: All, Anyone started matching the geocoded data to their patient addresses? It does not seem as easy as matching the address, zip and city across the two tables. G George Kowalski BS Biomedical Engineer Biomedical Informatics (414) 805-7318<tel:%28414%29%20805-7318> (office) [email protected]<mailto:[email protected]><mailto:[email protected]<mailto:[email protected]>> _______________________________________________ Gpc-dev mailing list [email protected]<mailto:[email protected]> http://listserv.kumc.edu/mailman/listinfo/gpc-dev<https://urldefense.proofpoint.com/v2/url?u=http-3A__listserv.kumc.edu_mailman_listinfo_gpc-2Ddev&d=DwMFaQ&c=aFamLAsxMIDYjNglYHTMV0iqFn3z4pVFYPQkjgspw4Y&r=C_iDrPTjMjNVakiaVcSiVk-6jAlbEoLBTtmMo_3OTb0&m=kabeuQD5KC2KyQ-R5p5IQ7JFdrSZYL_G1qyfPyrmUUU&s=H4z96CtHJzHa2vZZTA4D_Tn0QXhA49Px1hcPghFEFaU&e=>
_______________________________________________ Gpc-dev mailing list [email protected] http://listserv.kumc.edu/mailman/listinfo/gpc-dev
