This query is used to count drugs (drug_concept_id) across all drug 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 drug era start date. The input to the query is a value (or a comma-separated list of values) of a drug_concept_id , year, age_group (10 year age band) and gender_concept_id. If the input is omitted, all existing value combinations are summarized.
Input:
| Parameter |
Example |
Mandatory |
Notes |
| list of concept_id |
1300978, 1304643, 1549080 |
Yes |
|
| list of year_of_era |
2007, 2008 |
Yes |
|
Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
SELECT
tt.drug_concept_id,
count(1) as s_count, tt.age_band, tt.year_of_Era, tt.gender_concept_id
from (
SELECT
floor( (extract(year from t.drug_era_start_date )
- p.year_of_birth
)/10
) as age_band,
extract(year from t.drug_era_start_date) as year_of_era,
p.gender_concept_id,
t.drug_concept_id
FROM drug_era t,
person p
where t.person_id = p.person_id
and t.drug_concept_id in (1300978, 1304643, 1549080)
) tt
where tt.age_band in(3,4) and tt.year_of_Era in( 2007, 2008)
group by tt.age_band, tt.year_of_Era, tt.gender_concept_id, tt.drug_concept_id
order by tt.age_band, tt.year_of_Era, tt.gender_concept_id, tt.drug_concept_id
;
Output:
Output field list:
| Field |
Description |
| drug_concept_id |
A foreign key that refers to a standard concept identifier in the vocabulary for the drug concept. |
| age_band |
The number of individual drug exposure occurrences used to construct the drug era. |
| year_of_era |
A foreign key to the predefined concept identifier in the vocabulary reflecting the type of drug exposure recorded. It indicates how the drug exposure was represented in the source data: as medication history, filled prescriptions, etc.
|
| gender_concept_id |
A foreign key that refers to a standard concept identifier in the vocabulary for the gender of the person. |
| drug_era_start_date |
The start date for the drug era constructed from the individual instances of drug exposures. It is the start date of the very first chronologically recorded instance of utilization of a drug. |
| year_of_era |
|
| person_id |
A foreign key identifier to the person who is subjected to the drug during the drug era. The demographic details of that person are stored in the person table. |
| year_of_birth |
The year of birth of the person. For data sources with date of birth, the year is extracted. For data sources where the year of birth is not available, the approximate year of birth is derived based on any age group categorization available. |
Sample output record:
| Field |
Description |
| drug_concept_id |
|
| age_band |
|
| year_of_era |
|
| gender_concept_id |
|
| drug_era_start_date |
|
| year_of_era |
|
| person_id |
|
| year_of_birth |
|
|
|
