Common Query Patterns

Dates

When operational data is transferred to Redshift, many date concepts are persisted in VARCHAR columns with the format YYYY-MM-DD HH:MI:SS. VARCHAR columns that manage date concepts can be converted to Redshift TIMESTAMPZ as needed as in the following query.

select cast(admitdate as timestamptz) from dbo.encounters where admitdate is not null limit 1

All Encounters for a Person using Demographics

It is important to consider that all demographic information is related to a row in the patients table. To locate all data (in this example, encounters) related to the person (unique human individual) to which a patient is related, use the following query pattern.

Use this pattern on any data table to find rows that may not share a patient_id but are related to the same person_id.

select e.*
from dbo.encounters e
join dbo.personrecord pr on pr.patient_id = e.patient_id
join dbo.patients p on p.id = pr.patient_id
join dbo.demographicnames dn on dn.demographic_id = p.demographic_id
where dn.firstname = 'Jane' and dn.lastname = 'Doe'

All Lab Results for a Person with Mapped Terms

Lab results are stored in the labobservations table and use a term (labobservations.observationterm_id) to represent the coded value that describes the test performed. The term_id stored in this column describes the term sent by the source system. This source term will subsequently be mapped to related reference terms in a canonical code systems such as LOINC. To search for all lab results that are related to a reference term, use the termmap view as demonstrated in the following query pattern.

Use this pattern on any data table to find rows with terms that are mapped to a specified reference term.

select *
from dbo.labobservations lo
join dbo.termmap t on t.termid = lo.observationterm_id
where t.mappedcode = '4548-4'
and t.mappedcodesystem = 'LOINC'

All Lab Results with a Mapped Term in a Value Set

To obtain all lab results for a test that is managed within in a particular value set, use the valueset view as demonstrated in the following query pattern. This query will obtain lab results with observationterm_ids that are mapped to reference terms that are contained within the specified value set.

Use this pattern on any data table to find rows with terms in a specified value set.

select *
from dbo.labobservations lo
join dbo.valueset v on v.termid = lo.observationterm_id
where v.name = 'HEDIS MY 2020 HbA1C Tests (96070-8) [Panel]'