SQL on FHIR
3.0.0-ballot - STU 3 Ballot International flag

SQL on FHIR, published by HL7 International / FHIR Infrastructure. This guide is not an authorized publication; it is the continuous build for version 3.0.0-ballot built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/HL7/sql-on-fhir/ and changes regularly. See the Directory of published versions

Library: Unique Patient Addresses

Official URL: http://hl7.org/fhir/uv/sql-on-fhir/Library/UniquePatientAddressesQuery Version: 3.0.0-ballot
Standards status: Informative Computable Name: UniquePatientAddressesQuery
Other Identifiers: OID:2.16.840.1.113883.4.642.40.77.28.5

Retrieves each patient's most recent address in a given city. Includes two dialects for the same logical query.

Standard SQL:

WITH ranked_addresses AS (
  SELECT
    patient_view.id AS patient_id,
    patient_view.name,
    patient_address_view.address_line1,
    patient_address_view.city,
    patient_address_view.state,
    patient_address_view.postal_code,
    patient_address_view.updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY patient_view.id
      ORDER BY patient_address_view.updated_at DESC, patient_address_view.address_id DESC
    ) AS address_rank
  FROM patient_view
  JOIN patient_address_view
    ON patient_view.id = patient_address_view.patient_id
  WHERE patient_view.active = true
    AND patient_address_view.city = :city
)
SELECT
  patient_id,
  name,
  address_line1,
  city,
  state,
  postal_code
FROM ranked_addresses
WHERE address_rank = 1

PostgreSQL dialect:

SELECT DISTINCT ON (patient_view.id)
  patient_view.id AS patient_id,
  patient_view.name,
  patient_address_view.address_line1,
  patient_address_view.city,
  patient_address_view.state,
  patient_address_view.postal_code
FROM patient_view
JOIN patient_address_view
  ON patient_view.id = patient_address_view.patient_id
WHERE patient_view.active = true
  AND patient_address_view.city = :city
ORDER BY patient_view.id, patient_address_view.updated_at DESC, patient_address_view.address_id DESC

Metadata
Title Unique Patient Addresses
Version 3.0.0-ballot
Identifier urn:oid:2.16.840.1.113883.4.642.40.77.28.5
Jurisdiction World
Steward (Publisher) HL7 International / FHIR Infrastructure
Steward Contact HL7 International / FHIR Infrastructure
Description

Retrieves each patient's most recent address in a given city. Includes two dialects for the same logical query.

Standard SQL:

WITH ranked_addresses AS (
  SELECT
    patient_view.id AS patient_id,
    patient_view.name,
    patient_address_view.address_line1,
    patient_address_view.city,
    patient_address_view.state,
    patient_address_view.postal_code,
    patient_address_view.updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY patient_view.id
      ORDER BY patient_address_view.updated_at DESC, patient_address_view.address_id DESC
    ) AS address_rank
  FROM patient_view
  JOIN patient_address_view
    ON patient_view.id = patient_address_view.patient_id
  WHERE patient_view.active = true
    AND patient_address_view.city = :city
)
SELECT
  patient_id,
  name,
  address_line1,
  city,
  state,
  postal_code
FROM ranked_addresses
WHERE address_rank = 1

PostgreSQL dialect:

SELECT DISTINCT ON (patient_view.id)
  patient_view.id AS patient_id,
  patient_view.name,
  patient_address_view.address_line1,
  patient_address_view.city,
  patient_address_view.state,
  patient_address_view.postal_code
FROM patient_view
JOIN patient_address_view
  ON patient_view.id = patient_address_view.patient_id
WHERE patient_view.active = true
  AND patient_address_view.city = :city
ORDER BY patient_view.id, patient_address_view.updated_at DESC, patient_address_view.address_id DESC
Type sql-query from http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes
Dependency Resource: https://example.org/ViewDefinition/patient_view
Canonical URL: https://example.org/ViewDefinition/patient_view
Dependency Resource: https://example.org/ViewDefinition/patient_address_view
Canonical URL: https://example.org/ViewDefinition/patient_address_view
Parameters
Name Use Card. Type Documentation
city In 0..1 string City to filter addresses
Library Content
application/sqlContent 
Encoded data
application/sql;dialect=postgresqlContent 
Encoded data
Generated using version 0.5.4 of the sample-content-ig Liquid templates