Remote Subscriber Database (RSD) SQL guide (Compass 1)
Introduction
This article decribes the steps we take to create, and analyse, patient data selections from the Discovery database.
To add this data to a report, we create datasets. Each dataset represents the result set 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 we receive a list of patients marked for observational study, is to create a patient cohort. Patient data comes usually comes into the DDS in the form of pseudonymised personal data. Using the pseudo identifier or the pseudo NHS identifier we can obtain additional information about the patient from the Discovery database.
The following entity diagram shows the relationships between patient and organisation:
We link the patients to the Discovery database by using their pseudo identifiers. However, if only the pseudo NHS numbers are provided then we can derive the corresponding pseudo identifiers from the Link Distributor table. We filter the patients by their organisation ODS codes to ensure only patients of the required GP practices are selected