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

Introduction

This article decribes 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:

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:

RTENOTITLE

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.

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