Remote Subscriber Database (RSD) SQL guide (Compass 1)

Introduction

This article describes the steps needed to create, and analyse, patient data selections from the Discovery database.

Datasets are needed to add patient data to a report. Each dataset represents the results gained from running a combination of SQL commands and stored procedures on a data source.

The following diagram shows the algorithm that is followed to generate these datasets:

Patient cohort

Creating the patient cohort

The first step, when a list of patients marked for observational study is received, is to create a patient cohort. Patient data usually comes into the DDS in the form of pseudonymised personal data. Additional information about the patient can be obtained from the Discovery database using the pseudo identifier or the pseudo NHS identifier.

The following entity diagram shows the relationships between patient and organisation:

Patients are linked to the Discovery database using pseudo patient identifiers. However, if only pseudo NHS numbers are provided, the corresponding pseudo identifiers can be derived from the link distributor table. Patients are filtered by organisation ODS codes to ensure that only the patients of the required GP practices are selected.

Building the patient cohort SQL with pseudo NHS numbers

The following example SQL shows how a patient cohort is built using pseudo NHS numbers:

Sometimes, the cohort is created with filtered observations.

The example below filters patients who are registered, alive, and have had diabetes. They belong to the age 12 years and over. Two additional tables are introduced for the query: observation(patients who have had diabetes) and episode of care (patients who are registered and have the appropriate registration type for this study).

Patients can be selected with a specific set of observations using a lookup table of relevant SNOMED codes.
Some patients prefer to withhold consent and are therefore are excluded from the cohort; the relevant SNOMED consent code can be checked in the observation table.
Patients are usually only select if they have registration type 2 (Regular/GMS).
Sometimes patients are also based on their age in years.

Building patient cohort SQL with filtered observations

Observation cohort

Creating the observation cohort

Observation cohort are created to improve query performance; instead of scanning through the entire observation table, a sub-set of the observation table is created, based on the patient cohort.

 Building the observation cohort SQL 

Medications cohort

Creating the medications cohort

Medications cohort are created to improve query performance; instead of scanning through the entire observation table, a sub-set of the medication statement table is created, based on the patient cohort.

 Building the medication cohort SQL

Other cohorts

Creating other cohorts

This step can apply to any large tables to support improved query performance. For example, the encounter_raw table is a good candidate to create a separate cohort for SQL query; you can query this table if you want information such as the number of face to face encounters, or the number of home visits.

 Building the encounter raw cohort SQL

 Building datasets

This involves creating a number of tables in the database.

Patient demographic dataset

A typical patient demographic dataset includes the following information: This information is translated into a 'create table' script, which is then run to create the dataset table in the database.

 Create table script for a patient demographic dataset table

 

 

 

 

In this example, the age and LSOA code are derived from the patient table. The demographic dataset table is updated with those values when there is a match on the pseudo identifier.
If the update is large, it might be possible to optimise the update process by using batches as shown in the following example: