Remote Subscriber Database (RSD) SQL guide (Compass 1): Difference between revisions
No edit summary |
No edit summary |
||
| Line 2: | Line 2: | ||
= Introduction = | = Introduction = | ||
This article decribes the steps | 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: | The following diagram shows the algorithm that is followed to generate these datasets: | ||
| Line 12: | Line 12: | ||
= Creating the Patient Cohort = | = Creating the Patient Cohort = | ||
The first step, when | 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: | The following entity diagram shows the relationships between patient and organisation: | ||
| Line 18: | Line 18: | ||
[[File:Genral patient cohort.jpg|RTENOTITLE]] | [[File:Genral patient cohort.jpg|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 | The following example sql shows how a patient cohort is built using pseudo NHS numbers: | ||
[[File:Patient cohort sql.png]] | [[File:Patient cohort sql.png|RTENOTITLE]] | ||
| | ||
| | ||
Revision as of 16:48, 1 May 2020
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:
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:
