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

JoC at 14:32, 15 June 2020

2020-06-15T14:32:34Z

← Older revision		Revision as of 14:32, 15 June 2020
Line 1:		Line 1:
	~~== Introduction ==~~
	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.

			== Datasets ==
	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.		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.

JoC at 10:00, 10 June 2020

2020-06-10T10:00:54Z

← Older revision		Revision as of 10:00, 10 June 2020
Line 826:		Line 826:
	The following diagram shows the processing of the FilterObservations procedure:		The following diagram shows the processing of the FilterObservations procedure:

	~~===~~ [[File:FilterObservations procedure.png]] ~~===~~		[[File:FilterObservations procedure.png]]

	=== Calling FilterObservations Procedure ===		=== Calling FilterObservations Procedure ===
	{\| class="wikitable"		{\| class="wikitable"

AndreHui at 11:28, 9 June 2020

2020-06-09T11:28:45Z

Show changes

AndreHui: /* Building the patient cohort SQL with pseudo NHS numbers */

2020-06-09T11:12:39Z

Building the patient cohort SQL with pseudo NHS numbers

Show changes

JoC: JoC moved page Remote Subscriber Database (RSD) SQL guide to Remote Subscriber Database (RSD) SQL guide (Compass 1) without leaving a redirect

2020-06-08T15:08:32Z

JoC moved page Remote Subscriber Database (RSD) SQL guide to Remote Subscriber Database (RSD) SQL guide (Compass 1) without leaving a redirect

← Older revision	Revision as of 15:08, 8 June 2020
(No difference)

JoC: /* Updating Dataset Table Columns */

2020-06-08T15:07:24Z

Updating Dataset Table Columns

← Older revision		Revision as of 15:07, 8 June 2020
Line 1,098:		Line 1,098:

	=== Updating Dataset Table Columns ===		=== Updating Dataset Table Columns ===
	Using the result set derived from the above step, ~~we can now update~~ the dataset table columns with the appropriate observational values. This is illustrated in the code snippet of the populateCodeDateV2 procedure:		Using the result set derived from the above step, the dataset table columns are updated with the appropriate observational values.

			This is illustrated in the code snippet of the populateCodeDateV2 procedure:<syntaxhighlight lang="sql">
			DROP TEMPORARY TABLE IF EXISTS qry_tmp;
			CREATE TEMPORARY TABLE qry_tmp (
			row_id INT,
			group_by VARCHAR(255),
			original_code VARCHAR(20),
			original_term VARCHAR(200),
			clinical_effective_date DATE, PRIMARY KEY(row_id)
			) AS
			SELECT (@row_no := @row_no+1) AS row_id,
			f.group_by,
			f.original_code,
			f.original_term,
			f.clinical_effective_date
			FROM filteredObservationsV2 f, (SELECT @row_no := 0) t;
			SET @row_id = 0;
			WHILE EXISTS (SELECT row_id from qry_tmp
			WHERE row_id > @row_id AND row_id <= @row_id + 1000) DO
			SET @sql = CONCAT('UPDATE ', datasetTable,
			' d JOIN qry_tmp f ON d.pseudo_id = f.group_by SET ',
			col, "Code = f.original_code, ",
			col, "Term = f.original_term, ",
			col, "Date = date_format(f.clinical_effective_date, '%d/%m/%Y')
			WHERE f.row_id > @row_id AND f.row_id <= @row_id + 1000");
			PREPARE stmt FROM @sql;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
			SET @row_id = @row_id + 1000;
			END WHILE;
			</syntaxhighlight>

JoC: /* Calling the storeSnomedString procedure */

2020-06-08T15:00:54Z

Calling the storeSnomedString procedure

← Older revision		Revision as of 15:00, 8 June 2020
Line 661:		Line 661:
	[[File:SNOMED stored procedures.png]]		[[File:SNOMED stored procedures.png]]

	=== Calling the storeSnomedString procedure ===		==== Calling the storeSnomedString procedure ====
	The first step of the procedure is to find all the SNOMED codes given a set of parent codes from the input parameter. We pass the codes into the procedure as a comma separated list.		The first step of the procedure is to find all the SNOMED codes given a set of parent codes from the input parameter. We pass the codes into the procedure as a comma separated list.

Line 700:		Line 700:
	</syntaxhighlight>We repeat this process for each CAT ID flag passed.		</syntaxhighlight>We repeat this process for each CAT ID flag passed.

	=== Calling the getAllSnomedsFromSnomedString procedure ===		==== Calling the getAllSnomedsFromSnomedString procedure ====

	We call another procedure getAllSnomedsFromSnomedString to loop through the temporary table, and depending on the CAT ID flag being passed, we retrieve all the corresponding child codes.<syntaxhighlight lang="sql">		We call another procedure getAllSnomedsFromSnomedString to loop through the temporary table, and depending on the CAT ID flag being passed, we retrieve all the corresponding child codes.<syntaxhighlight lang="sql">
	USE data_extracts;		USE data_extracts;
Line 986:		Line 987:
	</syntaxhighlight>		</syntaxhighlight>

	=== Calling the createobservationsFromCohort procedure ===		==== Calling the createobservationsFromCohort procedure ====
	The createobservationsFromCohort procedure is called from the FilterObservations procedure; the filter type is passed as the input parameter:		The createobservationsFromCohort procedure is called from the FilterObservations procedure; the filter type is passed as the input parameter:
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
Line 1,057:		Line 1,058:
	</syntaxhighlight>This returns a new observations cohort based on the SNOMED codes list.		</syntaxhighlight>This returns a new observations cohort based on the SNOMED codes list.

	=== Filter Observations by Timeframe ===		==== Filter Observations by Timeframe ====

	Using the new observations cohort, we pass in the filter type to retrieve the observations based on the timeframe, and using the clinical effective date to determine the time intervals. We store the result set into a temporary table. This is illustrated in the code snippet of the filterObservationsV2 procedure below:<syntaxhighlight lang="sql">		Using the new observations cohort, we pass in the filter type to retrieve the observations based on the timeframe, and using the clinical effective date to determine the time intervals. We store the result set into a temporary table. This is illustrated in the code snippet of the filterObservationsV2 procedure below:<syntaxhighlight lang="sql">
	CREATE TABLE filteredObservationsV2 as		CREATE TABLE filteredObservationsV2 as
Line 1,095:		Line 1,097:
	</syntaxhighlight>		</syntaxhighlight>

	== Updating Dataset Table Columns ==		=== Updating Dataset Table Columns ===
	Using the result set derived from the above step, we can now update the dataset table columns with the appropriate observational values. This is illustrated in the code snippet of the populateCodeDateV2 procedure:		Using the result set derived from the above step, we can now update the dataset table columns with the appropriate observational values. This is illustrated in the code snippet of the populateCodeDateV2 procedure:

JoC at 14:58, 8 June 2020

2020-06-08T14:58:29Z

@@ Line 1,055: / Line 1,055: @@
 END//
 DELIMITER ;
 </syntaxhighlight>

JoC at 14:30, 8 June 2020

2020-06-08T14:30:26Z

← Older revision		Revision as of 14:30, 8 June 2020
Line 994:		Line 994:
	DELIMITER //		DELIMITER //
	CREATE PROCEDURE createObservationsFromCohortV2 (		CREATE PROCEDURE createObservationsFromCohortV2 (
	IN filterType INT		IN filterType INT
	-- 0 earliest, 1 latest, 2 ever, 3 pivot around cohort_gh2 pivot date (6		-- 0 earliest, 1 latest, 2 ever, 3 pivot around cohort_gh2 pivot date (6
	months)		months)
	)		)
Line 1,002:		Line 1,002:
	CREATE TEMPORARY TABLE gh2_snomeds_tmp AS		CREATE TEMPORARY TABLE gh2_snomeds_tmp AS
	SELECT cat_id,		SELECT cat_id,
	snomed_id		snomed_id
	FROM gh2_snomeds		FROM gh2_snomeds
	WHERE cat_id IN (3, 4);		WHERE cat_id IN (3, 4);
Line 1,011:		Line 1,011:
	IF (filterType = 3) THEN		IF (filterType = 3) THEN
	-- pivot over 6 months from pivot date (already set in cohort)		-- pivot over 6 months from pivot date (already set in cohort)
	CREATE TABLE observationsFromCohortV2 AS		CREATE TABLE observationsFromCohortV2 AS
	SELECT DISTINCT		SELECT DISTINCT
	o.id,		o.id,
	o.patient_id,		o.patient_id,
	o.person_id,		o.person_id,
	o.group_by,		o.group_by,
	o.clinical_effective_date,		o.clinical_effective_date,
	o.original_code,		o.original_code,
	SUBSTRING(o.original_term, 1, 200) AS original_term,		SUBSTRING(o.original_term, 1, 200) AS original_term,
	o.result_value,		o.result_value,
	o.result_value_units,		o.result_value_units,
	o.age_years		o.age_years
	FROM cohort_gh2_observations o JOIN cohort_gh2 c		FROM cohort_gh2_observations o JOIN cohort_gh2 c
	ON o.group_by = c.group_by		ON o.group_by = c.group_by
	WHERE EXISTS (SELECT 'x' FROM gh2_snomeds s		WHERE EXISTS (SELECT 'x' FROM gh2_snomeds s
	WHERE s.cat_id IN (1, 2)		WHERE s.cat_id IN (1, 2)
	AND s.snomed_id = o.original_code)		AND s.snomed_id = o.original_code)
	AND o.clinical_effective_date IS NOT NULL		AND o.clinical_effective_date IS NOT NULL
	AND o.clinical_effective_date		AND o.clinical_effective_date
	BETWEEN DATE_SUB(c.pivot_date, INTERVAL 6 MONTH)		BETWEEN DATE_SUB(c.pivot_date, INTERVAL 6 MONTH)
	~~This returns a new observations cohort based on the SNOMED codes list.~~		AND DATE_SUB(c.pivot_date, INTERVAL -6 MONTH);
	~~Filter Observations by Timeframe~~
	~~Using the new observations cohort, we pass in the filter type to retrieve the observations based~~
	~~on the timeframe, and using the clinical effective date to determine the time intervals. We store~~
	~~the result set into a temporary table. This is illustrated in the code snippet of the~~
	~~filterObservationsV2 procedure below:~~
	AND DATE_SUB(c.pivot_date, INTERVAL -6 MONTH);
	ELSE		ELSE
	-- latest or earliest or ever (so all observations)		-- latest or earliest or ever (so all observations)
	CREATE TABLE observationsFromCohortV2 AS		CREATE TABLE observationsFromCohortV2 AS
	SELECT DISTINCT		SELECT DISTINCT
	o.id,		o.id,
	o.patient_id,		o.patient_id,
	o.person_id,		o.person_id,
	o.group_by,		o.group_by,
	o.clinical_effective_date,		o.clinical_effective_date,
	o.original_code,		o.original_code,
	SUBSTRING(o.original_term, 1, 200) AS original_term,		SUBSTRING(o.original_term, 1, 200) AS original_term,
	o.result_value,		o.result_value,
	o.result_value_units,		o.result_value_units,
	o.age_years		o.age_years
	FROM cohort_gh2_observations o		FROM cohort_gh2_observations o
	WHERE EXISTS (SELECT 'x' FROM gh2_snomeds s		WHERE EXISTS (SELECT 'x' FROM gh2_snomeds s
	WHERE s.cat_id IN (1, 2)		WHERE s.cat_id IN (1, 2)
	AND s.snomed_id = o.original_code)		AND s.snomed_id = o.original_code)
	AND o.clinical_effective_date IS NOT NULL;		AND o.clinical_effective_date IS NOT NULL;
	END IF;		END IF;
	ALTER TABLE observationsFromCohortV2 ADD INDEX gh2_obv_pat_idx (patient_id);		ALTER TABLE observationsFromCohortV2 ADD INDEX gh2_obv_pat_idx (patient_id);
	END//		END//
	DELIMITER ;		DELIMITER ;
	~~CREATE TABLE filteredObservationsV2 as~~
	~~SELECT~~
	~~ob.id,~~
	~~ob.group_by,~~
	~~ob.patient_id,~~
	~~ob.person_id,~~
	~~ob.original_code,~~
	~~ob.original_term,~~
	~~ob.result_value,~~
	~~ob.clinical_effective_date,~~
	~~ob.result_value_units,~~
	~~ob.age_years,~~
	~~ob.rnk~~
	~~FROM (~~
	~~SELECT o.id,~~
	~~o.group_by,~~
	~~o.patient_id,~~
	~~o.person_id,~~
	~~o.original_code,~~
	~~o.original_term,~~
	~~o.result_value,~~
	~~o.clinical_effective_date,~~
	~~o.result_value_units,~~
	~~o.age_years,~~
	~~@currank := IF(@curperson = o.person_id,~~
	~~@currank + 1, 1) AS rnk,~~
	~~@curperson := o.person_id AS cur_person~~
	~~FROM observationsFromCohortV2 o,~~
	~~(SELECT @currank := 0, @curperson := 0) r~~
	~~WHERE o.result_value IS NOT NULL~~
	~~ORDER BY o.person_id, o.clinical_effective_date ASC,~~
	~~o.id ASC -- earliest~~
	~~) ob~~
	~~WHERE ob.rnk = 1;~~

	</syntaxhighlight>		</syntaxhighlight>

JoC at 14:08, 8 June 2020

2020-06-08T14:08:24Z

Show changes