Prakash, I think this is how we did it... ah... I see that in some cases we used a fabricated encounter number:
excerpt from epic_flowsheets_transform.sql<https://informatics.kumc.edu/work/browser/heron_load/epic_flowsheets_transform.sql>: create or replace view flowsheet_measurement as select ifr.record_date , ifm.recorded_time , cpeh.pat_id , ifgd.flo_meas_name , ifm.meas_value , ifgd.units -- , ifgd.value_type_name -- deprecated in Clarity 2014 favor using val_type_c , zcvt.name as value_type_name -- , ifgd.multi_select_yn -- deprecated in Clarity 2014 for multi_sel_yn , ifgd.multi_sel_yn , ifm.flo_meas_id , cpeh.pat_enc_csn_id , ifm.entry_time , ifm.fsd_id , ifm.line flowsheet_line -- i2b2 equivalents common to many/all datatypes , case when cpeh.pat_enc_csn_id is not null then to_char(cpeh.pat_enc_csn_id) else 'fabricated_for_' || cpeh.pat_id end ENCOUNTER_IDE , cpeh.pat_id PATIENT_IDE , 'KUH|FLO_MEAS_ID:' || ifm.flo_meas_id CONCEPT_CD , '@' PROVIDER_ID -- todo , recorded_time START_DATE , '@' MODIFIER_CD -- TODO: test that max(ifm.line) < 100000; it's 7079 as of this writing , ifm.fsd_id * 100000 + ifm.line instance_num , '@' valueflag_cd -- TODO: [H]igh/[L]ow/[A]bnormal , ifgd.units UNITS_CD , ifm.recorded_time END_DATE , '@' LOCATION_CD , to_number(null) CONFIDENCE_NUM -- TODO: think confidence_num through , entry_time UPDATE_DATE , mod(ora_hash(cpeh.pat_id), &&heron_etl_chunks)+1 as part from CLARITY.ip_flwsht_meas ifm join CLARITY.ip_flwsht_rec ifr on ifr.fsd_id = ifm.fsd_id join CLARITY.ip_flo_gp_data ifgd on ifm.flo_meas_id= ifgd.flo_meas_id join (select min(pat_enc_csn_id) as pat_enc_csn_id, inpatient_data_id, pat_id from clarity.pat_enc cpeh group by inpatient_data_id, pat_id) cpeh on ifr.inpatient_data_id=cpeh.inpatient_data_id left join CLARITY.zc_val_type zcvt on ifgd.val_type_c = zcvt.val_type_c; -- Dan
_______________________________________________ Gpc-dev mailing list [email protected] http://listserv.kumc.edu/mailman/listinfo/gpc-dev
