Hi! I was wondering how the covid index is calculated. I am trying to match a visit occurrence to the covid index but I am getting multiple visits that align with the covid index date. If possible, could someone clarify how the covid index date is extracted from the data. Thank you!

Created by Sarah Pungitore uastudent
Hi @uastudent , Covid index is calculated by finding the first available SARS-COV-2 PCR positive test in the measurement table for each patient (i.e. the earliest date they were confirmed covid positive). Here is the SQL Query we used: ``` SELECT DISTINCT p.person_id, p.year_of_birth, CASE WHEN covid_pos_records.codeset_id=651620200 THEN covid_pos_records.measurement_date ELSE NULL END AS covid_index FROM person p INNER JOIN ( SELECT DISTINCT m.*, c.codeset_id FROM measurement m INNER JOIN concept_set_members c ON c.concept_id=m.measurement_concept_id INNER JOIN concept_set_members c2 ON c2.concept_id=m.value_as_concept_id WHERE c.codeset_id IN (651620200) AND c2.codeset_id=400691529 ) AS covid_pos_records ON covid_pos_records.person_id=p.person_id ```

Covid Index page is loading…