View source for Remote Subscriber Database (RSD) SQL guide (Compass 1)


= Introduction =

This article decribes the steps we take to create, and analyse, patient&nbsp;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:

[[File:Dataset algorithm.jpg|600x600px|The algorithm followed to generate 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&nbsp;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&nbsp;the relationships between patient and organisation:

[[File:Genral patient cohort.jpg|RTENOTITLE]]

We link patients to the Discovery database using the patient&nbsp;pseudo identifiers. However, if only pseudo NHS numbers are provided,&nbsp;we can derive the corresponding pseudo identifiers from the '''Link Distributor''' table. We filter the patients by their organisation ODS codes to ensure that only patients of the required GP practices are selected.

&nbsp;

&nbsp;