MIT-LCP / eicu-website

Repository moved
https://github.com/MIT-LCP/eicu-code
MIT License
3 stars 3 forks source link

Null date/times in medications table populated with offset of "0" #34

Open obadawi opened 7 years ago

obadawi commented 7 years ago

We recently discovered that the ETL transforming raw eCM data into eRI data converts null date/time fields into "0" which will then appear to have an offset equal to the time of ICU admission. We believe this mainly affects the medications table which may often have null stop dates when medications are ordered but are trying to investigate further. This is something we can fix in a future eRI release but I doubt it can be updated in the current CRD release. We should add comments to the documentation to inform users of this and we can add more clarity to it as we learn more about what areas are affected.

jraffa commented 7 years ago

I put this together to help understand the extent this may be problematic.

table offset cols affected total_rows pct_affected
admissiondrug drugoffset, drugenteredoffset 2327 874920 0.3
admissiondx admitdxenteredoffset 1373 626858 0.2
allergy allergyoffset, allergyenteredoffset 1105 251949 0.4
autoacuityoverridehistory timestampoffset 2 34141 0.0
autoacuityquickentry lastsavedoffset 4 104045 0.0
careplancareprovider careprovidersaveoffset 2674 502765 0.5
careplaneol cpleolsaveoffset, cpleoldiscussionoffset 675 1433 47.1
careplangeneral cplitemoffset 41959 3115018 1.3
careplangoal cplgoaloffset 575 504139 0.1
careplaninfectiousdisease cplinfectdiseaseoffset 12 8056 0.1
customlab labotheroffset 0 1082 0.0
diagnosis diagnosisoffset 414 2710672 0.0
infusiondrug infusionoffset 1248 4803719 0.0
intakeoutput intakeoutputoffset, intakeoutputentryoffset 5722 12994887 0.0
lab labresultoffset, labresultrevisedoffset 22129 39132531 0.1
medication drugorderoffset, drugstartoffset, drugstopoffset 289542 7301853 4.0
microlab culturetakenoffset 52 16996 0.3
note noteoffset, noteenteredoffset 16127 3835897 0.4
nurseassessment nurseassessoffset, nurseassessentryoffset 12342 18255000 0.1
nursecare nursecareoffset, nursecareentryoffset 7479 13618762 0.1
nursecharting nursingchartoffset, nursingchartentryoffset 144017 221108200 0.1
pasthistory pasthistoryoffset, pasthistoryenteredoffset 5740 1149180 0.5
patient hospitaladmitoffset, hospitaldischargeoffset, unitdischargeoffset 7967 200859 4.0
physicalexam physicalexamoffset 31200 9873332 0.3
respiratorycare respcarestatusoffset, ventstartoffset, ventendoffset, priorventstartoffset, priorventendoffset 865381 865381 100.0
respiratorycharting respchartoffset, respchartentryoffset 21139 26747237 0.1
smartalert triggeroffset, reactivateoffset 378158 596603 63.4
ssadmf expirationoffset 0 473 0.0
treatment treatmentoffset 671 3688745 0.0
vitalaperiodic observationoffset 12278 25075074 0.0
vitalperiodic observationoffset 13484 146671642 0.0