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_id
s 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]'