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
```