This query is used to count conditions (condition_concept_id) across all condition era records stratified by year, age group and gender (gender_concept_id). The age groups are calculated as 10 year age bands from the age of a person at the condition era start date. The input to the query is a value (or a comma-separated list of values) of a condition_concept_id , year, age_group (10 year age band) and gender_concept_id. If the input is ommitted, all existing value combinations are summarized..
Input:
| Parameter |
Example |
Mandatory |
Notes |
| condition_concept_id |
|
|
|
Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
SELECT season, count(*) AS cases
FROM /* Extrinsic Asthma/season */
( SELECT CASE
WHEN to_number( to_char( condition_era_start_date, 'DDD' ) )
BETWEEN 1 AND to_number( to_char( to_date( '21-MAR-2001' ), 'DDD' ) )
THEN 'Winter'
WHEN to_number( to_char( condition_era_start_date, 'DDD' ) )
BETWEEN to_number( to_char( to_date( '22-MAR-2001' ), 'DDD' ) )
AND to_number( to_char( to_date( '21-JUN-2001' ), 'DDD' ) )
THEN 'Spring'
WHEN to_number( to_char( condition_era_start_date, 'DDD' ) )
BETWEEN to_number( to_char( to_date( '22-JUN-2001' ), 'DDD' ) )
AND to_number( to_char( to_date( '21-SEP-2001' ), 'DDD' ) )
THEN 'Summer'
WHEN to_number( to_char( condition_era_start_date, 'DDD' ) )
BETWEEN to_number( to_char( to_date( '22-SEP-2001' ), 'DDD' ) )
AND to_number( to_char( to_date( '21-DEC-2001' ), 'DDD' ) )
THEN 'Fall'
WHEN to_number( to_char( condition_era_start_date, 'DDD' ) )
>= to_number( to_char( to_date( '22-DEC-2001' ), 'DDD' ) )
THEN 'Winter'
END AS season
FROM condition_era
JOIN /* Extrinsic Asthma ICD-9 493.* Get associated SNOMed codes
with their decendents */
( SELECT DISTINCT descendant_concept_id AS snomed_asthma
FROM vocabulary.source_to_concept_map map
JOIN vocabulary.concept_ancestor ON ancestor_concept_id = target_concept_id
WHERE source_vocabulary_id = 2 /* icd9 */
AND target_vocabulary_id = 1 /* SNOMed */
AND source_code LIKE '493.0%'
AND sysdate BETWEEN valid_start_date AND valid_end_date
AND primary_map = 'Y'
) ON snomed_asthma = condition_concept_id
) GROUP BY season;
Output:
Output field list:
| Field |
Description |
| condition_era_start_date |
The start date for the condition era constructed from the individual instances of condition occurrences. It is the start date of the very first chronologically recorded instance of the condition. |
| descendant_concept_id |
A foreign key to the concept code in the concept table for the lower-level concept that forms the descendant in the relationship. |
| valid_start_date |
The date when the mapping instance was first recorded. |
| valid_end_date |
The date when the mapping instance became invalid because it was deleted or superseded (updated) by a new relationship. Default value is 31-Dec-2099. |
| condition_concept_id |
A foreign key that refers to a standard condition concept identifier in the vocabulary. |
Sample output record:
| Field |
Description |
| condition_era_start_date |
|
| descendant_concept_id |
|
| valid_start_date |
|
| valid_end_date |
|
| condition_concept_id |
|
|
|
