COC09: Condition that is regionally dependent

 Parameter  Example  Mandatory  Notes
source_code 088.81 Yes lyme disease

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue

SELECT state, count(*) total_enroled, sum( lymed ) lyme_cases
     , TRUNC( ( sum(lymed) /count(*) ) * 100, 2 ) AS percent
  FROM --person, state, lyme diagnosis
     ( SELECT person_id, state, NVL( lymed, 0 ) lymed
         FROM person
         JOIN location USING( location_id )
         LEFT OUTER JOIN -- patient with lyme disease
            ( SELECT DISTINCT person_id, 1 AS lymed
                FROM condition_era
                JOIN vocabulary.source_to_concept_map ON target_concept_id = condition_concept_id
               WHERE source_vocabulary_id = 2 
                 AND target_vocabulary_id = 1
                 AND source_code = '088.81' /* lyme disease */
                 AND sysdate BETWEEN valid_start_date and valid_end_date
                 AND primary_map ='Y'
            ) USING( person_id )
 GROUP BY state ORDER BY percent DESC;
Output field list:
 Field  Description
condition_concept_id A foreign key that refers to a standard condition concept identifier in the vocabulary.
state The state field as it appears in the source data.
valid_start_date The date when the concept was first recorded.
valid_end_date The date when the concept became invalid because it was deleted or superseded (updated) by a new concept. The default value is 31-Dec-2099.

Sample output record:
 Field  Description