AndreHui at 09:38, 18 June 2020

2020-06-18T09:38:53Z

← Older revision		Revision as of 09:38, 18 June 2020
Line 664:		Line 664:
	</syntaxhighlight>		</syntaxhighlight>

	==== ~~'''~~Retrieving the SNOMED Concept Ids~~'''~~ ====		==== Retrieving the SNOMED Concept Ids ====
	The procedure accepts a list of SNOMED codes as input parameter.		The procedure accepts a list of SNOMED codes as input parameter.

AndreHui at 09:34, 18 June 2020

2020-06-18T09:34:09Z

Show changes

Jay at 16:51, 17 June 2020

2020-06-17T16:51:30Z

← Older revision		Revision as of 16:51, 17 June 2020
Line 786:		Line 786:
	Then we run separate update SQL queries to populate the dataset table by calling multiple stored procedures.		Then we run separate update SQL queries to populate the dataset table by calling multiple stored procedures.

	= 6. Calling Stored Procedures =		= Calling Stored Procedures =
	Once we have generated the observation and medication datasets and cohorts, the next step is to populate the datasets using stored procedures.		Once we have generated the observation and medication datasets and cohorts, the next step is to populate the datasets using stored procedures.

Jay: Formatting SQL into Code Blocks

2020-06-17T16:46:14Z

Formatting SQL into Code Blocks

Show changes

Jay: Formatting SQL into Code Blocks

2020-06-17T16:10:44Z

Formatting SQL into Code Blocks

Show changes

Jay at 15:47, 17 June 2020

2020-06-17T15:47:17Z

← Older revision		Revision as of 15:47, 17 June 2020
Line 1:		Line 1:
	This article describes the steps needed to create, and analyse, patient data selections from the Discovery database.		This article describes the steps needed to create, and analyse, patient data selections from the Discovery database.

	= 1. Introduction =		= Introduction =
	This outlines the steps we take to create the patient selection for analysis from the Discovery database.		This outlines the steps we take to create the patient selection for analysis from the Discovery database.

Line 10:		Line 10:
	[[File:RSD SQL Guide C2 Fig 1.1.jpg\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%201.1.jpg\|frameless\|633x633px]]		[[File:RSD SQL Guide C2 Fig 1.1.jpg\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%201.1.jpg\|frameless\|633x633px]]

	= 2. Creating the Patient Cohort =		= Creating the Patient Cohort =
	This is the first step when we receive a list of patients marked for observational study.		This is the first step when we receive a list of patients marked for observational study.

Line 20:		Line 20:
	We filter patients by their organization ODS codes to ensure only patients of the required GP practices are selected.		We filter patients by their organization ODS codes to ensure only patients of the required GP practices are selected.

	'''Example 1.1 Building patient cohort SQL with NHS numbers'''		'''Example 1.1 Building patient cohort SQL with NHS numbers'''<syntaxhighlight lang="sql">

	DROP TABLE IF EXISTS cohort_patient;		DROP TABLE IF EXISTS cohort_patient;

Line 43:		Line 42:

	-- add ods codes inside the brackets e.g. EMIS99, 5PC64		-- add ods codes inside the brackets e.g. EMIS99, 5PC64
			</syntaxhighlight>

	===== Cohort with Filtered Observations =====		===== Cohort with Filtered Observations =====

Jay: Formatting of images

2020-06-17T15:40:53Z

Formatting of images

← Older revision		Revision as of 15:40, 17 June 2020
Line 98:		Line 98:

	1. Create a lookup table to hold the consent codes:		1. Create a lookup table to hold the consent codes:
	[[File:~~RSD SQL Guide C2 Consent Codes~~.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Consent%20Codes.png\|~~left~~\|frameless\|448x448px]]
			[[File:RSD_SQL_Guide_C2_Consent_Codes.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Consent%20Codes.png\|alt=\|frameless\|448x448px]]

	2. Create a view to filter out the concept ids on the observation table with these codes:		2. Create a view to filter out the concept ids on the observation table with these codes:

Line 310:		Line 312:

	'''Fig 4.1 Entity diagram showing the relationships between patient, medication statement and organization'''		'''Fig 4.1 Entity diagram showing the relationships between patient, medication statement and organization'''
	[[File:~~Fig 4~~.1.png\|link=https://wiki.discoverydataservice.org/File:Fig%204.1.png\|~~left~~\|frameless\|633x633px]]
			[[File:Fig_4.1.png\|link=https://wiki.discoverydataservice.org/File:Fig%204.1.png\|alt=\|frameless\|633x633px]]

	'''Example 4.1 Building the Medications Cohort SQL'''		'''Example 4.1 Building the Medications Cohort SQL'''

Line 410:		Line 414:
	===== Patient Demographic Dataset =====		===== Patient Demographic Dataset =====
	A typical patient demographic dataset may consist of the following information:		A typical patient demographic dataset may consist of the following information:
	[[File:~~RSD SQL Guide C2 Example Patient Demographic Dataset~~.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Example%20Patient%20Demographic%20Dataset.png\|~~left~~\|frameless\|1294x1294px]]
			[[File:RSD_SQL_Guide_C2_Example_Patient_Demographic_Dataset.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Example%20Patient%20Demographic%20Dataset.png\|alt=\|frameless\|1294x1294px]]

	For this example, we create the dataset to hold the patient demographic information as follows:		For this example, we create the dataset to hold the patient demographic information as follows:

Line 588:		Line 594:
	===== Patient Observations Dataset =====		===== Patient Observations Dataset =====
	A typical patient observations dataset may ask for the following information:		A typical patient observations dataset may ask for the following information:
	[[File:~~RSD SQL Guide C2 Example Patient Observations dataset~~.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Example%20Patient%20Observations%20dataset.png\|~~left~~\|frameless\|1122x1122px]]
			[[File:RSD_SQL_Guide_C2_Example_Patient_Observations_dataset.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Example%20Patient%20Observations%20dataset.png\|alt=\|frameless\|1122x1122px]]

	A typical dataset can have over 100 to 200 fields. Usually, there will be several datasets to deliver. But this illustrates the kind of layout we use for constructing an observational dataset.		A typical dataset can have over 100 to 200 fields. Usually, there will be several datasets to deliver. But this illustrates the kind of layout we use for constructing an observational dataset.

Line 750:		Line 758:

	'''Fig 5.1 Shows the naming convention of a dataset column'''		'''Fig 5.1 Shows the naming convention of a dataset column'''
	[[File:~~RSD SQL Guide C2 Fig 5~~.1.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%205.1.png\|~~left~~\|frameless\|744x744px]]
			[[File:RSD_SQL_Guide_C2_Fig_5.1.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%205.1.png\|alt=\|frameless\|744x744px]]

	Again, we pre-populate the dataset with the unique identifiers from the patient cohort.		Again, we pre-populate the dataset with the unique identifiers from the patient cohort.

Line 757:		Line 767:
	===== Patient Medications Dataset =====		===== Patient Medications Dataset =====
	A typical medication request would look like this:		A typical medication request would look like this:
	[[File:~~RSD SQL Guide C2 Medication Request~~.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Medication%20Request.png\|~~left~~\|frameless\|1235x1235px]]
			[[File:RSD_SQL_Guide_C2_Medication_Request.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Medication%20Request.png\|alt=\|frameless\|1235x1235px]]

	For this example, we create the dataset to hold the medication information as follows:		For this example, we create the dataset to hold the medication information as follows:

Line 788:		Line 800:

	'''Fig 6.1 Shows the datasets update process using stored procedures'''		'''Fig 6.1 Shows the datasets update process using stored procedures'''
	~~[[File:RSD SQL Guide C2 Fig 6.1.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%206.1.png\|left\|frameless\|653x653px]]~~

			[[File:RSD_SQL_Guide_C2_Fig_6.1.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%206.1.png\|alt=\|frameless\|653x653px]]<br />
	=== Calling the Populate Stored Procedures ===		=== Calling the Populate Stored Procedures ===
	Create a SQL script to call the stored procedure as follows:		Create a SQL script to call the stored procedure as follows:
Line 1,049:		Line 1,061:

	'''Fig 6.2 Shows how the SNOMED codes are being retrieved using stored procedures'''		'''Fig 6.2 Shows how the SNOMED codes are being retrieved using stored procedures'''
	~~[[File:RSD SQL Guide C2 Fig 6.2.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%206.2.png\|left\|frameless\|871x871px]]~~

			[[File:RSD_SQL_Guide_C2_Fig_6.2.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%206.2.png\|alt=\|frameless\|871x871px]]<br />
	===== Calling the storeSnomedString procedure =====		===== Calling the storeSnomedString procedure =====
	We call the storeSnomedString procedure to separate the list of SNOMED codes into their individual values and store them into a temporary table:		We call the storeSnomedString procedure to separate the list of SNOMED codes into their individual values and store them into a temporary table:
Line 1,285:		Line 1,297:

	Match the parent against the value in the target column. This will return all the children in the source column.		Match the parent against the value in the target column. This will return all the children in the source column.
	[[File:~~RSD SQL Guide C2 Concepts tct~~.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Concepts%20tct.png\|~~left~~\|frameless]]
			[[File:RSD_SQL_Guide_C2_Concepts_tct.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Concepts%20tct.png\|alt=\|frameless]]

	==== Retrieving filtered Observations ====		==== Retrieving filtered Observations ====
Line 1,291:		Line 1,304:

	'''Fig 6.3 Shows the processing of the FilterObservations procedure'''		'''Fig 6.3 Shows the processing of the FilterObservations procedure'''
	~~[[File:RSD SQL Guide C2 Fig 6.3.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%206.3.png\|left\|frameless\|729x729px]]~~

			[[File:RSD_SQL_Guide_C2_Fig_6.3.png\|link=https://wiki.discoverydataservice.org/File:RSD%20SQL%20Guide%20C2%20Fig%206.3.png\|alt=\|frameless\|729x729px]]<br />
	===== Calling the filterObservations procedure =====		===== Calling the filterObservations procedure =====
	By calling the filterObservations procedure, we pass in the following input parameters:		By calling the filterObservations procedure, we pass in the following input parameters:

Jay: Added intro line

2020-06-17T15:24:09Z

Added intro line

← Older revision		Revision as of 15:24, 17 June 2020
Line 1:		Line 1:
			This article describes the steps needed to create, and analyse, patient data selections from the Discovery database.

	= 1. Introduction =		= 1. Introduction =
	This outlines the steps we take to create the patient selection for analysis from the Discovery database.		This outlines the steps we take to create the patient selection for analysis from the Discovery database.

Jay: Created page

2020-06-17T15:20:39Z

Created page

Show changes

Remote Subscriber Database (RSD) SQL guide (Compass 2) - Revision history

AndreHui at 09:38, 18 June 2020

AndreHui at 09:34, 18 June 2020

Jay at 16:51, 17 June 2020

Jay: Formatting SQL into Code Blocks

Jay: Formatting SQL into Code Blocks

Jay at 15:47, 17 June 2020

Jay: Formatting of images

Jay: Added intro line

Jay: Created page