Our source_visit_ids are bit messed up as it was extracted from two different hospital systems and had some tech issues which resulted in lack of unique identifier. So, we would like to create our own visit_occurrence_ids to avoid all this confusion and keep it clean.
If it’s only for visit_occurrence table, I can just populate a normal sequence number for visit_occurrence_ids,
But the challenge is when I have to identify the corresponding lab, drug info for each visit?
may I check how does one link that newly generated visit_occurrence_ids to existing labs, drugs and diganosis info.
If I go by date interval, I have to check one row against multiple rows of that patient and then assign the newly created visit_occurrence_id to identified row (in lab/drug/diag table). Don’t know this appraoch feels inefficient and may/may not work for big data.
Or infact, do you compare one row against all others rows of the patient and assign the visit_id? (by making use of your computational resources)
I did read multiple OMOP implementation papers but looks like not many went through this challenge.
Any advice or tips from CDM implementers who had similar scenario and what was the logic that you followed (which we can try to replicate).