Remote Subscriber Database (RSD) SQL guide (Compass 2)

This article describes the steps needed to create, and analyse, patient data selections from the Discovery database.

Introduction

This outlines the steps we take to create the patient selection for analysis 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.

Fig 1.1 shows the algorithm we follow to generate these datasets

Creating the Patient Cohort

This is the first step when we receive a list of patients marked for observational study.

General Patient Cohort

Fig 2.1 Entity diagram showing the relationships between patient and organization

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

DROP TABLE IF EXISTS cohort_patient;

CREATE TABLE cohort_patient AS
SELECT
       p.id AS patient_id,
       p.nhs_number,
       p.person_id AS group_by,
       p.organization_id,
       p.date_of_birth -- add additional columns if required   
FROM patient p JOIN organization org ON p.organization_id = org.id
WHERE org.ods_code IN ('EMIS99','5PC64');  

-- add ods codes inside the brackets e.g. EMIS99, 5PC64

Cohort with Filtered Observations

Sometimes, the cohort is created with filtered observations.

In the example below, we create a cohort where the patients are registered, alive and have diabetes. They belong to age 12 and over.

Here we have introduced additional tables for the query: observation, concept and the episode of care.

Fig 2.2 Entity diagram showing the relationships between patient, observation and episode of care

By joining the observation table we can obtain the observational information about the patient. If specific information is required, like in this case we are looking for patients with diabetes, we would need to filter the observation based on that specific clinical term. That could be in the form of a stand alone code or a list of codes: SNOMED, READ, CTV3, etc.

The observation table does not store these codes directly but store them as concept ids. On querying the data then, the concept table will be queried first to get the list of concept ids that you are interested in for this observation.

If you intend to filter observations by SNOMEDS then add in the concept map table which will map any legacy concept ids, that is, READ, CTV3 etc, to the SNOMED concept ids, then you can filter by SNOMED codes as shown: 

SELECT
  ...

FROM patient p
JOIN observation o on o.patient_id = p.id
JOIN concept_map cm on cm.legacy = o.non_core_concept_id
-- use the non core concept id for link to the concept table
JOIN concept c on c.dbid = cm.core
WHERE c.code IN (46635009, 44054006) -- e.g. diabetes snomed codes

From the episode of care, we can obtain whether the patient is currently registered and has the appropriate registration type for this study:

SELECT
  ...

FROM patient p
JOIN episode_of_care e ON e.patient_id = p.id
JOIN concept c ON c.dbid = e.registration_type_concept_id
WHERE c.code = 'R'  -- registered

Some patients prefer to withhold consent and therefore are excluded from the cohort. We can check this in the observation table. Again we need to get a list of concept ids that you are interested in for this observation.

For example, you could approach it in this way:

1.        Create a lookup table to hold the consent codes:

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

-- filter out the observation table with the relevant consent codes

DROP VIEW IF EXISTS obs_optout_view;

CREATE VIEW obs_optout_view AS
SELECT
       ob.id,
       ob.patient_id,
       ob.organization_id,
       ob.non_core_concept_id,
       ob.clinical_effective_date,
       cpt.dbid,
       cpt.code,
       cpt.name,
       csc.status
FROM observation ob
JOIN concept cpt ON cpt.dbid = ob.non_core_concept_id
JOIN consent_code csc ON cpt.id = csc.code
AND cpt.id = BINARY csc.code;

3.        Using the view to create a helper function to get the latest opt-out status of the patient:

-- function to retrieve the patient's latest opt out status

DROP FUNCTION IF EXISTS get_optout_status;
DELIMITER //
CREATE FUNCTION get_optout_status(p_patient_id BIGINT,
                                  p_organization_id BIGINT)
RETURNS VARCHAR(1)
NOT DETERMINISTIC READS SQL DATA

BEGIN

DECLARE l_status          VARCHAR(1);
DECLARE l_patient_id      BIGINT;
DECLARE l_organization_id BIGINT;

  SELECT
         ob.patient_id,
         ob.organization_id,
         IF (ob.status = 'Opt-Out', 1, IF(ob.status = 'Opt-In',null,null))
         INTO l_patient_id, l_organization_id, l_status
  FROM obs_optout_view ob
  LEFT JOIN obs_optout_view ob2 ON ob2.patient_id = ob.patient_id
  AND (ob.clinical_effective_date < ob2.clinical_effective_date
  OR (ob.clinical_effective_date = ob2.clinical_effective_date
       AND ob.id < ob2.id))
  WHERE ob2.patient_id IS NULL
  AND ob.patient_id = p_patient_id
  AND ob.organization_id = p_organization_id LIMIT 1;

RETURN l_status;
END//
DELIMITER ;

Where 1 = opt out, null = opt in or not recorded derived from the latest observation from the consent codes. Putting all of the above together, patient cohort query becomes this:

CREATE TABLE cohort_patient AS
  SELECT DISTINCT
         p.id         AS patient_id,
         p.person_id  AS group_by,
         get_optout_status (p.id, p.registered_practice_organization_id) AS optout_status,
         IF (p.date_of_death IS NULL,
              TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()),
              TIMESTAMPDIFF(YEAR, p.date_of_birth, p.date_of_death)) age_years
  FROM patient p
  JOIN observation o ON p.id = o.patient_id
  JOIN organization org ON org.id = o.organization_id
  JOIN episode_of_care e ON e.patient_id = o.patient_id
  JOIN concept c ON c.dbid = e.registration_type_concept_id
  JOIN (SELECT o.patient_id
        FROM observation o
        JOIN concept_map cm ON cm.legacy = o.non_core_concept_id
        JOIN concept c ON c.dbid = cm.core
        WHERE c.code IN (46635009, 44054006) -- e.g. snomed codes
        ) ob ON ob.patient_id = p.id
  WHERE org.ods_code IN ('EMIS99','5PC64') -- e.g. ods codes
  AND p.date_of_death IS NULL
  AND c.code = 'R'  -- registered patient
  AND e.date_registered <= now()
  AND (e.date_registered_end > now() OR
       e.date_registered_end IS NULL) -- active
  AND IF (p.date_of_death IS NULL,
          TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()),
          TIMESTAMPDIFF(YEAR, p.date_of_birth, p.date_of_death)) >= 12 -- age 12 and over
  AND get_optout_status(p.id, p.registered_practice_organization_id) IS NULL; -- opt in status

Creating the Observations Cohort

We create a separate observations cohort for query performance. Instead of scanning through the entire observation table, we create a sub-set of the observation table based on the patient cohort.

Example 2.1 Building the Observations Cohort SQL

CREATE TABLE cohort_observations AS
  SELECT DISTINCT
         o.id,
         o.person_id AS group_by,
         o.patient_id,
         o.person_id,
         o.clinical_effective_date,
         o.non_core_concept_id,
         o.result_value,
         o.result_value_units,
         o.result_date,
         o.result_text,
         cr.age_year
  FROM observation o JOIN cohort_patient cr
  ON o.person_id = cr.person_id
  AND o.organization_id = cr.organization_id
  AND o.patient_id = cr.patient_id;

CREATE INDEX obs_cpt_ix ON cohort_observation(non_core_concept_id);
CREATE INDEX obs_pat_ix ON cohort_observation(patient_id);

Creating the Medications Cohort

Again, we create the medications cohort for query performance. We create a sub-set of the medication statement/order table based on the patient cohort.

Fig 4.1 Entity diagram showing the relationships between patient, medication statement and organization

Example 4.1 Building the Medications Cohort SQL

-- step 1: cohort of the medication statement

DROP TABLE IF EXISTS cohort_medications_stmt;

CREATE TABLE cohort_medications_stmt AS
     SELECT DISTINCT
            m.id,
            m.non_core_concept_id,
            m.person_id,
            m.patient_id,
            m.clinical_effective_date,
            m.cancellation_date
     FROM medication_statement m
     JOIN cohort_patient cr
     ON m.organization_id = cr.organization_id
     AND m.person_id = cr.person_id
     AND m.patient_id = cr.patient_id;

CREATE INDEX med_stmt_ix ON cohort_medications_stmt(id);

-- step 2: cohort of the medication oder

DROP TABLE IF EXISTS cohort_medications_ord;

CREATE TABLE cohort_medications_ord AS  
     SELECT DISTINCT
            mo.id,
            mo.medication_statement_id
            mo.person_id,
            mo.patient_id,
            mo.clinical_effective_date
     FROM medication_order mo
     JOIN cohort_patient cr
     ON mo.organization_id = cr.organization_id
     AND mo.person_id = cr.person_id
     AND mo.patient_id = cr.patient_id;

CREATE INDEX med_ord_ix ON cohort_medications_ord(medication_statement_id);    

-- step 3: cohort of the medication - statement plus order

DROP TABLE IF EXISTS cohort_medications;

CREATE TABLE cohort_medications AS
     SELECT DISTINCT
            ms.id,
            ms.non_core_concept_id,
            ms.person_id,
            ms.patient_id,
            mo.clinical_effective_date,
            ms.cancellation_date
      FROM cohort_medications_stmt ms JOIN cohort_medications_ord mo
      ON ms.id = mo.medication_statement_id;

CREATE INDEX med_ix ON cohort_medications(non_core_concept_id);
CREATE INDEX med_pat_ix ON cohort_medications(patient_id);        

Building Datasets

This involves creating a number of temporary tables in the database.

Patient Demographic Dataset

A typical patient demographic dataset may consist of the following information:

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

CREATE TABLE demographicsDataset (
  ExtractDate                    DATETIME     NULL,
  Pseudo_id                      VARCHAR(255) NULL,   -- unique identifier
  Pseudo_NHSNumber               VARCHAR(255) NULL,
  Gender                         VARCHAR(50)  NULL,
  Age                            VARCHAR(50)  NULL,
  DateOfBirth                    DATE         NULL,
  EthnicityLCode                 VARCHAR(50)  NULL,
  EthnicityLTerm                 VARCHAR(200) NULL,
  BirthCountryLCode              VARCHAR(50)  NULL,
  BirthCountryLTerm              VARCHAR(200) NULL,
  RegistrationStart              DATE         NULL,
  RegistrationEnd                DATE         NULL,
  IMD2010                        VARCHAR(50)  NULL,
  LSOA2011                       VARCHAR(50)  NULL,
  PracticeODSCode                VARCHAR(50)  NULL,
  PracticeODSName                VARCHAR(255) NULL,
  CCGName                        VARCHAR(100) NULL,
  YearOfDeath                    INT(4)       NULL,
  F2fVisits_Total                INT(11)      DEFAULT 0,
  F2fVisits_1year                INT(11)      DEFAULT 0,
  F2fVisits_5years               INT(11)      DEFAULT 0
);

ALTER TABLE demographicsDataset ADD INDEX demo_pseudoid_idx (pseudo_id);

-- the unique identifier is usually the pseudo id
-- but to adapt for this schema we could use the person_id

We pre-populate the dataset with the unique identifiers from the patient cohort:

INSERT INTO demographicsDataset (pseudo_id, extractdate) SELECT DISTINCT group_by, now() FROM cohort_patient;

Then we run separate update SQL queries to populate the columns of the dataset table.

For example, add registration start date, registration end date, practice ods code, practice ods name:

DROP TEMPORARY TABLE IF EXISTS reg_sort;
DROP TEMPORARY TABLE IF EXISTS qry_reg;

CREATE TEMPORARY TABLE qry_reg AS
SELECT c.group_by,
       e.date_regirstered,
       e.date_registered_end,
       o.name,
       o.ods_code
FROM cohort_patient c
JOIN episode_of_care e ON e.person_id = c.group_by
JOIN organization o ON o.id = e.organization_id
WHERE o.ods_code IN ('EMIS99','5PC64'); -- e.g. ods codes


CREATE TEMPORARY TABLE reg_sort (
      row_id               INT,
      group_by             BIGINT,
      date_registered      DATE,
      date_regiostered_end DATE,
      ods_code             VARCHAR(50),
      name                 VARCHAR(255),
      rnk                  INT, PRIMARY KEY(row_id)) AS
SELECT (@row_no := @row_no + 1) AS row_id,
        a.group_by,
        a.date_registered,
        a.date_registered_end,
        a.ods_code,      
        a.name,
        a.rnk
FROM (SELECT q.group_by,
             q.date_registered,
             q.date_registered_end,
             q.name,
             q.ods_code,
             @currank := IF(@curperson = q.group_by, @currank + 1, 1) AS rnk,
             @curperson := q.group_by AS cur_person
      FROM qry_reg q, (SELECT @currank := 0, @curperson := 0) r
      ORDER BY q.group_by, q.date_registered DESC ) a,
      (SELECT @row_no := 0) t
WHERE a.rnk = 1;

SET @row_id = 0;

WHILE EXISTS (SELECT row_id from reg_sort
              WHERE row_id > @row_id AND row_id <= @row_id + 1000) DO

       UPDATE demographicsDataset d
       JOIN reg_sort reg ON d.pseudo_id = reg.group_by
       SET d.RegistrationStart = reg.date_registered,
           d.RegistrationEnd = reg.date_registered_end,
           d.PracticeODSCode = reg.ods_code,
           d.PracticeODSName = reg.name
       WHERE reg.row_id > @row_id
       AND reg.row_id <= @row_id + 1000;

       SET @row_id = @row_id + 1000;  

END WHILE;

In this example, we created 2 temporary tables. The first to hold the registration data. The second to select from the first the records which contain the latest registration date.

If the update is large in terms of the number of records to update then it might be possible to optimise the update process by using batches as demonstrated in the code example above.

Patient Observations Dataset

A typical patient observations dataset may ask for the following information:

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.

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

DROP TABLE IF EXISTS diagnoses2dataset;

CREATE TABLE diagnoses2dataset (
  Pseudo_id                                        VARCHAR(255) NULL,
  Pseudo_NHSNumber                                 VARCHAR(255) NULL,
  AsthmaECode                                      VARCHAR(50) NULL,
  AsthmaETerm                                      VARCHAR(200) NULL,
  AsthmaEDate                                      VARCHAR(50) NULL,
  AsthmaEmergeECode                                VARCHAR(50) NULL,
  AsthmaEmergeETerm                                VARCHAR(200) NULL,
  AsthmaEmergeEDate                                VARCHAR(50) NULL,
  AsthmaResolvedECode                              VARCHAR(50) NULL,
  AsthmaResolvedETerm                              VARCHAR(200) NULL,
  AsthmaResolvedEDate                              VARCHAR(50) NULL,
  COPDECode                                        VARCHAR(50) NULL,
  COPDETerm                                        VARCHAR(200) NULL,
  COPDEDate                                        VARCHAR(50) NULL,
  PulmonaryFibrosisECode                           VARCHAR(50) NULL,
  PulmonaryFibrosisETerm                           VARCHAR(200) NULL,
  PulmonaryFibrosisEDate                           VARCHAR(50) NULL,
  InterstitialLungDiseaseECode                     VARCHAR(50) NULL,
  InterstitialLungDiseaseETerm                     VARCHAR(200) NULL,
  InterstitialLungDiseaseEDate                     VARCHAR(50) NULL,
  AgeRelatedMuscularDegenerationECode              VARCHAR(50) NULL,
  AgeRelatedMuscularDegenerationETerm              VARCHAR(200) NULL,
  AgeRelatedMuscularDegenerationEDate              VARCHAR(50) NULL,
  GlaucomaECode                                    VARCHAR(50) NULL,
  GlaucomaETerm                                    VARCHAR(200) NULL,
  GlaucomaEDate                                    VARCHAR(50) NULL,
  RheumatoidArthritisECode                         VARCHAR(50) NULL,
  RheumatoidArthritisETerm                         VARCHAR(200) NULL,
  RheumatoidArthritisEDate                         VARCHAR(50) NULL,
  SystemicLupusECode                               VARCHAR(50) NULL,
  SystemicLupusETerm                               VARCHAR(200) NULL,
  SystemicLupusEDate                               VARCHAR(50) NULL,
  InflammatoryBowelDiseaseECode                    VARCHAR(50) NULL,
  InflammatoryBowelDiseaseETerm                    VARCHAR(200) NULL,
  InflammatoryBowelDiseaseEDate                    VARCHAR(50) NULL,
  CrohnsDiseaseECode                               VARCHAR(50) NULL,
  CrohnsDiseaseETerm                               VARCHAR(200) NULL,
  CrohnsDiseaseEDate                               VARCHAR(50) NULL,
  UlcerativeColitisCodeECode                       VARCHAR(50) NULL,
  UlcerativeColitisCodeETerm                       VARCHAR(200) NULL,
  UlcerativeColitisCodeEDate                       VARCHAR(50) NULL,
  AtopicDermatitisECode                            VARCHAR(50) NULL,
  AtopicDermatitisETerm                            VARCHAR(200) NULL,
  AtopicDermatitisEDate                            VARCHAR(50) NULL,
  InheritedMucociliaryClearanceECode               VARCHAR(50) NULL,
  InheritedMucociliaryClearanceETerm               VARCHAR(200) NULL,
  InheritedMucociliaryClearanceEDate               VARCHAR(50) NULL,
  PrimaryCiliaryDyskinesiaECode                    VARCHAR(50) NULL,
  PrimaryCiliaryDyskinesiaETerm                    VARCHAR(200) NULL,
  PrimaryCiliaryDyskinesiaEDate                    VARCHAR(50) NULL,
  MelanomaECode                                    VARCHAR(50) NULL,
  MelanomaETerm                                    VARCHAR(200) NULL,
  MelanomaEDate                                    VARCHAR(50) NULL,
  ProstateCancerECode                              VARCHAR(50) NULL,
  ProstateCancerETerm                              VARCHAR(200) NULL,
  ProstateCancerEDate                              VARCHAR(50) NULL,
  LungCancerECode                                  VARCHAR(50) NULL,
  LungCancerETerm                                  VARCHAR(200) NULL,
  LungCancerEDate                                  VARCHAR(50) NULL,
  SmallBowelCancerECode                            VARCHAR(50) NULL,
  SmallBowelCancerETerm                            VARCHAR(200) NULL,
  SmallBowelCancerEDate                            VARCHAR(50) NULL,
  ColorectalCancerECode                            VARCHAR(50) NULL,
  ColorectalCancerETerm                            VARCHAR(200) NULL,
  ColorectalCancerEDate                            VARCHAR(50) NULL,
  BreastCancerECode                                VARCHAR(50) NULL,
  BreastCancerETerm                                VARCHAR(200) NULL,
  BreastCancerEDate                                VARCHAR(50) NULL,
  MiscarriageECode                                 VARCHAR(50) NULL,
  MiscarriageETerm                                 VARCHAR(200) NULL,
  MiscarriageEDate                                 VARCHAR(50) NULL
);

ALTER TABLE diagnoses2dataset ADD INDEX pseudoid_idx (pseudo_id);

INSERT INTO diagnoses2dataset (pseudo_id)
SELECT DISTINCT group_by FROM cohort_patient;

Each column of the dataset is named by joining pieces of identifiable information together by combining the observational type, the timeframe for the analysis and the requested field type.

Fig 5.1 Shows the naming convention of a dataset column

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

Then we run separate update SQL queries to populate the dataset table by calling multiple stored procedures.

Patient Medications Dataset

A typical medication request would look like this:

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

CREATE TABLE medications (
  pseudo_id            VARCHAR(255) DEFAULT NULL,
  nhsnumber            VARCHAR(10)  DEFAULT NULL,
  codedate             VARCHAR(20)  DEFAULT NULL,
  codeterm             VARCHAR(255) DEFAULT NULL,
  code                 VARCHAR(100) DEFAULT NULL,
  codevalue            VARCHAR(100) DEFAULT NULL,
  codeunit             VARCHAR(100) DEFAULT NULL
);

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

Then we run separate update SQL queries to populate the dataset table by calling multiple 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.

We construct a series of populate stored procedures which in turn call on other stored procedures to complete the update process.

Fig 6.1 Shows the datasets update process using stored procedures

Calling the Populate Stored Procedures

Create a SQL script to call the stored procedure as follows:

CALL populateCodeDate
(0,'StillbirthE','diagnoses2dataset',0,'237364002,161743003',null,null,null,'N');
CALL populateCodeDate
(0,'PregnancyInducedHypertensionE','diagnoses2dataset',0,null,'48194001',null,null,'N');
CALL populateCodeDate
(0,'PreEclampsiaE','diagnoses2dataset',0,'398254007',null,null,null,'N');
CALL populateCodeDate
(0,'CholestasisE','diagnoses2dataset',0,'235888006',null,null,null,'N');
CALL populateCodeDate
(0,'GallstonesE','diagnoses2dataset',0,'266474003,407637009',null,null,null,'N');
CALL populateCodeDate
(0,'GoutE','diagnoses2dataset',0,'90560007,161451004',null,null,null,'N');
CALL populateCodeDate
(0,'AnkylosingSpondylitisE','diagnoses2dataset',0,'9631008',null,null,null,'N');
CALL populateCodeDate
(0,'JaundiceE','diagnoses2dataset',0,'18165001,161536006',null,null,null,'N');
CALL populateCodeDate
(0,'PsoriasisE','diagnoses2dataset',0,'9014002',null,null,null,'N');
CALL populateCodeDate
(0,'DeafnessE','diagnoses2dataset',0,'15188001',null,null,null,'N');
CALL populateCodeDate
(0,'HearingAidE','diagnoses2dataset',0,'365240006',null,null,null,'N');

The procedure accepts a list of input parameters (this may vary depending on requirements):

Parameter	Data Type	Description
filterType	INT	1 latest, 0 earliest, 2 ever, 3 pivot
col	VARCHAR(100)	the root of the column name
datasetTable	VARCHAR(100)	table name of dataset
reset	BIT	1 reset, 0 no reset of the dataset table
codesToAdd1	VARCHAR(5000)	include all snomed parents and their children
codesToAdd2	VARCHAR(5000)	include snomed parents only
codesToRemove3	VARCHAR(5000)	exclude snomed parents and their children
codesToRemove4	VARCHAR(5000)	exclude only snomed parents
ignorenulls	VARCHAR(1)	Y or N - whether to ignore null result value

A typical populate procedure is outlined below. (Please note this is only an example and may need modifications to it depending on your requirements).

DROP PROCEDURE IF EXISTS populateCodeDate;

DELIMITER //
CREATE PROCEDURE populateCodeDate (
IN filterType     INT, -- 1 latest, 0 earliest, 2 ever, 3 pivot
IN col            VARCHAR(100), -- the root of the column name
IN datasetTable   VARCHAR(100), -- table name of dataset
IN reset          BIT, -- 1 reset, 0 no reset
IN codesToAdd1    VARCHAR(5000), -- all parents and their children
IN codesToAdd2    VARCHAR(5000), -- parents only
IN codesToRemove3 VARCHAR(5000), -- parents and their children to be excluded
IN codesToRemove4 VARCHAR(5000), -- just parents to be excluded
IN ignorenulls    VARCHAR(1)
)

BEGIN

-- reset snomeds table
DELETE FROM snomeds WHERE cat_id IN (1, 2, 3, 4);
-- reset store table
DELETE FROM store WHERE id IN (1, 2, 3, 4);

-- get snomed concept ids

  IF codesToAdd1 IS NOT NULL THEN
      CALL storeSnomedString(codesToAdd1, 1);
      CALL getAllSnomedsConceptIds (1);
  END IF;

  IF codesToAdd2 IS NOT NULL THEN
      CALL storeSnomedString (codesToAdd2, 2);
      CALL getAllSnomedsConceptIds (2);
  END IF;

  IF codesToRemove3 IS NOT NULL THEN
      CALL storeSnomedString (codesToRemove3, 3);
      CALL getAllSnomedsConceptIds (3);
  END IF;

  IF codesToRemove4 IS NOT NULL THEN
      CALL storeSnomedString (codesToRemove4, 4);
      CALL getAllSnomedsConceptIds (4);
  END IF;

-- filter observation based on the snomed codes

CALL filterObservations(filterType, 1, ignorenulls);

-- reset columns

IF (reset = 1) THEN

  SET @reset_sql = CONCAT('UPDATE ', datasetTable, ' SET ',
  col, "Code = null, ",
  col, "Term = null, ",
  col, "Date = null");

  PREPARE resetStmt FROM @reset_sql;
  EXECUTE resetStmt;
  DEALLOCATE PREPARE resetStmt;

END IF;

  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 filteredObservations 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;
END//
DELIMITER ;

Retrieving the SNOMED Concept Ids

The procedure accepts a list of SNOMED codes as input parameter.

Fig 6.2 Shows how the SNOMED codes are being retrieved using stored procedures

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:

DROP TABLE IF EXISTS store;

CREATE TABLE store (
  id            INT,
  org_snomed_id BIGINT
);

ALTER TABLE store ADD INDEX store_idx (org_snomed_id);

DROP PROCEDURE IF EXISTS storeSnomedString;

DELIMITER //

CREATE PROCEDURE storeSnomedString (
  IN stringValue VARCHAR(5000),
  IN cat_id      INT
)

BEGIN

  DECLARE front       VARCHAR(5000) DEFAULT NULL;
  DECLARE frontlen    INT           DEFAULT NULL;
  DECLARE TempValue   VARCHAR(5000) DEFAULT NULL;

  processloop:
  LOOP

      IF LENGTH(TRIM(stringValue)) = 0
      OR stringValue IS NULL THEN
        LEAVE process loop;
      END IF;
   
  SET front = SUBSTRING_INDEX(stringValue, ',', 1);
  SET frontlen = LENGTH(front);
  SET TempValue = TRIM(front);

  INSERT INTO store (id, org_snomed_id)
  VALUES (cat_id, CAST(TempValue AS SIGNED));

  SET stringValue = INSERT(stringValue, 1, frontlen + 1, '');

  END LOOP;

END//
DELIMITER ;

Calling the getAllSnomedsConceptIds procedure

We call another procedure getAllSnomedsConceptIdsFromSnomedCodes to loop through the temporary table. For each SNOMED code, we retrieve the corresponding SNOMED concept id.

We store the resultant set of concept ids into another temporary table where it will be used to query observational data.

DROP TABLE IF EXISTS snomeds;

CREATE TABLE snomeds (
  cat_id               INT,
  snomed_concept_id    BIGINT,
  core_concept         INT,
  snomed_code          BIGINT,
  term                 VARCHAR(255)
);

ALTER TABLE snomeds ADD INDEX sno_cpt_idx(snomed_concept_id);
ALTER TABLE snomeds ADD INDEX sno_code_idx(snomed_code);

DROP PROCEDURE IF EXISTS getAllSnomedsConceptIds;

DELIMITER //
CREATE PROCEDURE getAllSnomedsConceptIds(p_cat_id INT)

BEGIN

  DECLARE done           INT;
  DECLARE l_parent_id    BIGINT;
  DECLARE c_get_snomeds  CURSOR FOR SELECT org_snomed_id
                         FROM store WHERE id = p_cat_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET done = 0;

       OPEN c_get_snomeds;

       processloop:
       WHILE (done = 0) DO

        FETCH c_get_snomeds INTO l_parent_id;

        IF done = 1 THEN
           LEAVE processloop;
        END IF;

        IF p_cat_id IN (1, 3) THEN

          -- get parent snomed concept id based on the snomed code

          INSERT INTO snomeds
          SELECT
                p_cat_id,
                cm.legacy,
                cm.core,
                c.code,
                c.name
           FROM concept_map cm JOIN concept c ON c.dbid = cm.core
           WHERE c.code = l_parent_id;

           -- get all child snomed concept ids based on the parent snomed code

           INSERT INTO snomeds
           SELECT
                 p_cat_id,
                 cptm.legacy,
                 cptm.core,
                 cpt.code,
                 cpt.name
            FROM concept_tct tc
            JOIN snomeds sn ON sn.core_concept = tc.target
            AND tc.property = '92842'
            JOIN concept_map cptm on cptm.core = tc.source
            AND tc.property = '92842'
            JOIN concept cpt on cpt.dbid = cptm.core
            WHERE sn.snomed_code = l_parent_id;

        ELSE

        -- include or exclude parent snomed

            INSERT INTO snomeds
            SELECT
                  p_cat_id,
                  cm.legacy,
                  cm.core,
                  c.code,
                  c.name
             FROM concept_map cm JOIN concept c ON c.dbid = cm.core
             WHERE c.code = l_parent_id;

        END IF;

       END WHILE processloop;
       CLOSE c_get_snomeds;

       SET done = 0;  

END //
DELIMITER ;

Using the concept_tct table, which is a transitive closure table, we are able to find the corresponding child concept ids based on the parent concept id.

Match the parent against the value in the target column. This will return all the children in the source column.

Retrieving filtered Observations

Using the prepared observations cohort, the next step is to filter out this cohort based on the SNOMED code list and the timeframe, which have been passed in as input parameters from the populate procedure.

Fig 6.3 Shows the processing of the FilterObservations procedure

Calling the filterObservations procedure

By calling the filterObservations procedure, we pass in the following input parameters:

Parameter	Data Type	Description
filterType	INT	Timeframe of the analysis. 0 = earliest, 1 = latest, 2 = ever, 3 = pivot around cohort pivot date (6 months), 4 = All since
toCreate	INT	1 is the default value - to call createObservationsFromCohort
ignorenulls	VARCHAR(1)	Y or N - whether to ignore null result value

DROP PROCEDURE IF EXISTS filterObservations;

DELIMITER //

CREATE PROCEDURE filterObservations (
     IN filterType INT,
-- 0 earliest, 1 latest, 2 ever,
-- 3 pivot around cohort pivot date (6 months),
-- 4 allSince
  IN toCreate   INT, -- 1 to createObservationsFromCohortv2
  IN ignorenulls VARCHAR(1) -- Y or N
  )

BEGIN

IF (toCreate = 1) THEN
  CALL createObservationsFromCohort (filterType);
END IF;

DROP TABLE IF EXISTS filteredObservations;

IF (filterType = 0) THEN -- earliest

  IF (ignorenulls = 'Y') THEN  

  CREATE TABLE filteredObservations 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 observationsFromCohort 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;

  ELSE

  CREATE TABLE filteredObservations 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 observationsFromCohort o,
          (SELECT @currank := 0, @curperson := 0) r
          ORDER BY o.person_id,
                   o.clinical_effective_date ASC,
                   o.id ASC -- earliest
        ) ob
  WHERE ob.rnk = 1;

  END IF;

ELSEIF (filterType = 2 OR filterType = 4) THEN -- ever or allSince

  IF (ignorenulls = 'Y') THEN

  CREATE TABLE filteredObservations AS
  SELECT DISTINCT
          mc.group_by,
          mc.patient_id,
          mc.person_id,
          mc.original_code,
          mc.original_term,
          mc.result_value,
          mc.clinical_effective_date,
          mc.result_value_units,
          mc.age_years
  FROM observationsFromCohort mc
  WHERE mc.result_value IS NOT NULL;

  ELSE

  CREATE TABLE filteredObservations AS
  SELECT DISTINCT
          mc.group_by,
          mc.patient_id,
          mc.person_id,
          mc.original_code,
          mc.original_term,
          mc.result_value,
          mc.clinical_effective_date,
          mc.result_value_units,
          mc.age_years
  FROM observationsFromCohort mc;

  END IF;


ELSEIF (filterType = 1 or filterType = 3) THEN -- latest or pivot

IF (ignorenulls = 'Y') THEN

CREATE TABLE filteredObservations 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 observationsFromCohort o,
          (SELECT @currank := 0, @curperson := 0) r
          WHERE o.result_value IS NOT NULL
          ORDER BY o.person_id,
                   o.clinical_effective_date DESC,
                   o.id DESC -- latest
        ) ob
  WHERE ob.rnk = 1;

ELSE

  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 observationsFromCohort o,
          (SELECT @currank := 0, @curperson := 0) r
          ORDER BY o.person_id,
                   o.clinical_effective_date DESC,
                   o.id DESC -- latest
         ) ob
  WHERE ob.rnk = 1;

END IF;

ELSE

  SIGNAL SQLSTATE '45000'

  SET MESSAGE_TEXT = 'filterType not recognised';

END IF;

END//
DELIMITER ;

Calling the createObservationsFromCohort procedure

From the filterObservations procedure, we call the createobservationsFromCohort procedure, passing in the filter type as the input parameter:

CALL createObservationsFromCohort (filterType);

DROP PROCEDURE IF EXISTS createObservationsFromCohort;

DELIMITER //

CREATE PROCEDURE createObservationsFromCohort (
  IN filterType INT
  -- 0 earliest, 1 latest, 2 ever,
  -- 3 pivot around cohort_patient pivot date (6 months)
)

BEGIN

DROP TEMPORARY TABLE IF EXISTS snomeds_tmp;

CREATE TEMPORARY TABLE snomeds_tmp AS
SELECT cat_id,
      snomed_concept_id
FROM snomeds
WHERE cat_id IN (3, 4);

-- delete from snomeds all snomed_concept ids to be excluded

DELETE t1 FROM snomeds t1 JOIN snomeds_tmp t2
ON t1.snomed_concept_id = t2.snomed_concept_id
WHERE t1.cat_id IN (1, 2);

DROP TABLE IF EXISTS observationsFromCohort;

IF (filterType = 3) THEN

-- pivot over 6 months from pivot date (already set in cohort)

CREATE TABLE observationsFromCohort AS
      SELECT DISTINCT
             o.id,
             o.patient_id,
             o.person_id,
             o.group_by,
             o.clinical_effective_date,
             o.non_core_concept_id AS original_code,
             SUBSTRING(s.term, 1, 200) AS original_term,
             o.result_value,
             o.result_value_units,
             o.age_years
     FROM cohort_observations o
          JOIN snomeds s ON s.snomed_concept_id = o.non_core_concept_id
     WHERE o.clinical_effective_date IS NOT NULL
     AND o.clinical_effective_date
     BETWEEN DATE_SUB(c.pivot_date, INTERVAL 6 MONTH)
     AND DATE_SUB(c.pivot_date, INTERVAL -6 MONTH);

ELSE

-- latest or earliest or ever (so all observations)

CREATE TABLE observationsFromCohort AS
     SELECT DISTINCT
            o.id,
            o.patient_id,
            o.person_id,
            o.group_by,
            o.clinical_effective_date,
            o.non_core_concept_id AS original_code,
            SUBSTRING(s.term, 1, 200) AS original_term,
            o.result_value,
            o.result_value_units,
            o.age_years
     FROM cohort_observations o
          JOIN snomeds s ON s.snomed_concept_id = o.non_core_concept_id
     WHERE o.clinical_effective_date IS NOT NULL;

END IF;

ALTER TABLE observationsFromCohort ADD INDEX obv_pat_idx(patient_id);

END//

DELIMITER ;

This returns a new observations cohort based on the SNOMED code list.

Filter Observations by Timeframe

Using the new observations cohort, we pass in the filter type to retrieve the observations based on the timeframe for the analysis, 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 filterObservations procedure below: 

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 observationsFromCohort 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;

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 populateCodeDate procedure:

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;

To populate medication datasets, we use a similar approach as above.

APPENDIX

Some supplementary material that maybe helpful when developing SQL query, in particular, working with concept ids.

Helper Functions

•          Function to retrieve the description field on the concept table based on the concept id:

DELIMITER //

CREATE FUNCTION get_concept_desc(p_concept_id int)
RETURNS VARCHAR(400)
NOT DETERMINISTIC READS SQL DATA
BEGIN

DECLARE l_description VARCHAR(400);

SELECT description INTO l_description
FROM concept cpt
WHERE cpt.dbid = p_concept_id;

RETURN l_description;

END//
DELIMITER ;

Usage: SELECT get_concept_desc(pat.gender_concept_id) FROM patient pat;

•          Function to retrieve the code field on the concept table based on the concept id:

DROP FUNCTION IF EXISTS get_concept_code;

DELIMITER //

CREATE FUNCTION get_concept_code(p_concept_id int)
RETURNS VARCHAR(20)
NOT DETERMINISTIC READS SQL DATA
BEGIN

DECLARE l_code VARCHAR(20);

SELECT code INTO l_code
FROM concept cpt
WHERE cpt.dbid = p_concept_id;

RETURN l_code;

END//
DELIMITER ;

Usage: SELECT get_concept_code(epoc.registration_type_concept_id)
       FROM episode_of_care epoc;

•          Function to retrieve the scheme field on the concept table based on the concept id:

DROP FUNCTION IF EXISTS get_concept_scheme;

DELIMITER //

CREATE FUNCTION get_concept_scheme(p_concept_id int)
RETURNS VARCHAR(50)
NOT DETERMINISTIC READS SQL DATA
BEGIN

DECLARE l_scheme VARCHAR(50);

SELECT scheme INTO l_scheme
FROM concept cpt
WHERE cpt.dbid = p_concept_id;

RETURN l_scheme;

END//
DELIMITER ;

Usage: SELECT get_concept_scheme(ob.non_core_concept_id) FROM observation ob;

•          Function to retrieve the ods code based on the organization id:

DROP FUNCTION IF EXISTS get_ods;

DELIMITER //

CREATE FUNCTION get_ods(p_org_id bigint)
RETURNS VARCHAR(50)
NOT DETERMINISTIC READS SQL DATA
BEGIN

DECLARE l_ods_code VARCHAR(50);

SELECT ods_code INTO l_ods_code
FROM organization org
WHERE org.id = p_org_id;

RETURN l_ods_code;

END//
DELIMITER ;

Usage: SELECT get_ods(pat.registered_practice_organization_id) FROM patient pat;

•          Function to retrieve the organization name based on the organization id:

DROP FUNCTION IF EXISTS get_org_name;

DELIMITER //

CREATE FUNCTION get_org_name(p_org_id bigint)
RETURNS VARCHAR(255)
NOT DETERMINISTIC READS SQL DATA

BEGIN

DECLARE l_org_name VARCHAR(255);

SELECT name INTO l_org_name
FROM organization org
WHERE org.id = p_org_id;

RETURN l_org_name;

END//
DELIMITER ;

Usage: SELECT get_ods(pat.registered_practice_organization_id) FROM patient pat;

•          Function to retrieve the event type from the description of the concept table:

DROP FUNCTION IF EXISTS get_event_type;

DELIMITER //

CREATE FUNCTION get_event_type(p_description VARCHAR(400))
RETURNS VARCHAR(50)
NOT DETERMINISTIC NO SQL
BEGIN

DECLARE l_event VARCHAR(50);

SELECT substr(p_description,instr(p_description,"(") + 1,
       instr(p_description,")") - instr(p_description,"(") - 1)
       INTO l_event;

RETURN l_event;

END//
DELIMITER ;

Usage: SELECT get_event_type(get_concept_desc(ob.core_concept_id)) event_type
       FROM observation ob;

•          Function to retrieve the latest medication order issue date based on the medication statement id:

DROP FUNCTION IF EXISTS get_lastest_med_issue_date;

DELIMITER //

CREATE FUNCTION get_lastest_med_issue_date (p_medication_stmt_id bigint)
RETURNS DATE
NOT DETERMINISTIC READS SQL DATA
BEGIN

DECLARE l_date DATE;
DECLARE l_rnk  INT;

-- get latest issue date

  SELECT
          med.clinical_effective_date, med.rnk INTO l_date, l_rnk
  FROM (
          SELECT
            mo.id,
            mo.medication_statement_id,
            mo.clinical_effective_date,
            @currank :=
            IF(@curmedstmt = mo.medication_statement_id,@currank + 1,1) AS rnk,
            @curmedstmt := mo.medication_statement_id AS cur_med_stmt
          FROM medication_order mo,(SELECT @currank := 0, @curmedstmt := 0) r
          WHERE mo.medication_statement_id = p_medication_stmt_id
          ORDER BY mo.medication_statement_id DESC,
                   mo.clinical_effective_date DESC,
                   mo.id DESC -- latest
        ) med
  WHERE med.rnk = 1;

RETURN l_date;

END//
DELIMITER ;

Usage: SELECT DATE_FORMAT(get_lastest_med_issue_date(med.id),"%Y%m%d") date_of_last_issue
       FROM medication_statement med;

•          Function to retrieve the earliest medication order issue date based on the medication statement id:

DROP FUNCTION IF EXISTS get_earliest_med_issue_date;

DELIMITER //

CREATE FUNCTION get_earliest_med_issue_date (p_medication_stmt_id bigint)
RETURNS DATE
NOT DETERMINISTIC READS SQL DATA

BEGIN

DECLARE l_date DATE;
DECLARE l_rnk  INT;

-- get earliest issue date

  SELECT
          med.clinical_effective_date, med.rnk INTO l_date, l_rnk
  FROM (
          SELECT
            mo.id,
            mo.medication_statement_id,
            mo.clinical_effective_date,
            @currank :=
            IF(@curmedstmt = mo.medication_statement_id,@currank + 1,1) AS rnk,
            @curmedstmt := mo.medication_statement_id AS cur_med_stmt
          FROM medication_order mo,(SELECT @currank := 0, @curmedstmt := 0) r
          WHERE mo.medication_statement_id = p_medication_stmt_id
          ORDER BY mo.medication_statement_id ASC,
                   mo.clinical_effective_date ASC,
                   mo.id ASC -- earliest
        ) med
  WHERE med.rnk = 1;

RETURN l_date;

END//
DELIMITER ;

Usage: SELECT DATE_FORMAT(get_earliest_med_issue_date(med.id),"%Y%m%d") issue_date
       FROM medication_statement med;

•          Function to retrieve the event log last changed date based on the record id:

DROP FUNCTION IF EXISTS get_event_maxdate;

DELIMITER //

CREATE FUNCTION get_event_maxdate (p_table_id tinyint, p_record_id bigint)
RETURNS DATETIME
READS SQL DATA
BEGIN

DECLARE l_max_date DATETIME;

SELECT  MAX(etlg.dt_change) modified_date INTO l_max_date
FROM event_log etlg
WHERE etlg.table_id = p_table_id AND record_id = p_record_id;

RETURN l_max_date;

END//
DELIMITER ;

Usage: SELECT get_event_maxdate(2,pat.id) FROM patient pat;
       Where 2 is the table id for the patient table.

•          Function to retrieve the event log last changed date for medication order based on the medication statement id:

DROP FUNCTION IF EXISTS get_event_maxdate_medo;

DELIMITER //

CREATE FUNCTION get_event_maxdate_medo (p_table_id tinyint, p_record_id bigint)
RETURNS datetime
READS SQL DATA
BEGIN

DECLARE l_max_date datetime;

SELECT MAX(etlg.dt_change) INTO l_max_date
FROM event_log etlg JOIN medication_order mo
ON mo.medication_statement_id = p_record_id
AND mo.id = etlg.record_id
WHERE etlg.table_id = p_table_id;

RETURN l_max_date;

END//
DELIMITER ;

Usage: SELECT get_event_maxdate_medo (9,med.id) FROM medication_statement med

SQL Statement Examples

Patient Information

SELECT
     pat.id patient_id,
     get_ods(pat.registered_practice_organization_id) source_organization,
     IF (pat.date_of_death IS NULL,
        TIMESTAMPDIFF(YEAR,pat.date_of_birth,CURDATE()),
        TIMESTAMPDIFF(YEAR,pat.date_of_birth,pat.date_of_death)) age,
     get_concept_desc(pat.gender_concept_id) sex,
     DATE_FORMAT(epoc.startregdate,"%Y%m%d") start_regdate,
     get_concept_code(epoc.registration_type_concept_id)  reg_status,
     pat.nhs_number nhsnumber,
     pad.postcode,
     DATE_FORMAT(pat.date_of_birth,"%Y%m%d") date_of_birth,
     epoc.usualgp,
     pat.first_names forename,
     pat.last_name surname,
     get_concept_desc(pat.ethnic_code_concept_id) ethnic_category,
     DATE_FORMAT(pat.date_of_death,"%Y%m%d") date_of_death,
     DATE_FORMAT(epoc.endregdate,"%Y%m%d") end_reg_date,
     get_optout_status(pat.id, pat.registered_practice_organization_id) optout_status,
     pad.address_line_1,
     pad.address_line_2,
     pad.address_line_3,
     pad.address_line_4,
     pad.city,
     NULLIF(GREATEST(COALESCE(get_event_maxdate(2,pat.id),0),
                     COALESCE(get_event_maxdate(20,pad.id),0),
                     COALESCE(get_event_maxdate(6,epoc.id),0)),0) last_modified_date
FROM patient pat
LEFT JOIN patient_address pad ON pat.current_address_id = pad.id AND pat.id = pad.patient_id
LEFT JOIN
  (SELECT
          epc.id,
          epc.organization_id,
          epc.patient_id,
          epc.registration_type_concept_id,
          epc.registration_status_concept_id,
          epc.date_registered startregdate,
          epc.date_registered_end endregdate,
          epc.usual_gp_practitioner_id,
          prac.name usualgp
  FROM episode_of_care epc LEFT JOIN practitioner prac
  ON prac.id = epc.usual_gp_practitioner_id
  AND prac.organization_id = epc.organization_id) epoc
  ON epoc.patient_id = pat.id
  AND epoc.organization_id = pat.registered_practice_organization_id;

Prescription Information

SELECT
     med.id event_id,
     get_ods(med.organization_id) source_organization,
     med.patient_id patient_id,
     pat.nhs_number nhs_number,
     get_concept_desc(med.authorisation_type_concept_id)  issue_type,
     DATE_FORMAT(get_earliest_med_issue_date(med.id),"%Y%m%d") issue_date,
     DATE_FORMAT(get_lastest_med_issue_date(med.id),"%Y%m%d") date_of_last_issue,
     DATE_FORMAT(med.clinical_effective_date,"%Y%m%d") recorded_date,
     get_concept_code(med.core_concept_id) dmdcode,
     med.dose dosage,
     med.quantity_value quantity,
     med.quantity_unit quantity_unit,
     get_concept_desc(med.core_concept_id) dmd_description,
     NULLIF(GREATEST(COALESCE(get_event_maxdate(10,med.id),0),
                     COALESCE(get_event_maxdate_medo(9,med.id),0)),0) last_modified_date
FROM medication_statement med JOIN patient pat ON med.patient_id = pat.id;

Clinical Event Information

SELECT
     ob.id event_id,
     get_ods(ob.organization_id) source_organization,
     ob.patient_id,
     pat.nhs_number nhs_number,
     DATE_FORMAT(ob.clinical_effective_date,"%Y%m%d") effective_date,
     get_concept_code(ob.non_core_concept_id) original_code,
     get_concept_desc(ob.non_core_concept_id) original_term,
     get_concept_desc(get_concept_scheme(ob.non_core_concept_id)) original_code_scheme,
     ob.result_value result_value,
     CASE
     WHEN ob.is_problem = 1 AND ob.is_review = 0 THEN 'N - New'
     WHEN ob.is_problem = 1 AND ob.is_review = 1 THEN 'F - Follow Up'
     ELSE 'O - Other'
     END AS episode_type,
     prac.role_desc hcp_role,
     enc.type encounter_type,
     get_concept_code(ob.core_concept_id) snomed_concept_id,
     get_concept_desc(ob.core_concept_id) snomed_term,
     ob.parent_observation_id parent_event,
     get_ods(enc.organization_id) event_location,
     get_org_name(enc.organization_id) location_name,
     enc.appointment_id appointment_slot_id,
     get_event_type(get_concept_desc(ob.core_concept_id)) event_type,
    NULLIF(GREATEST(COALESCE(get_event_maxdate(11,ob.id),0),
                    COALESCE(get_event_maxdate(5,enc.id),0),
                    COALESCE(get_event_maxdate(13,prac.id),0)),0) last_modified_date
FROM observation ob JOIN patient pat ON ob.patient_id = pat.id
LEFT JOIN practitioner prac ON prac.id = ob.practitioner_id
LEFT JOIN encounter enc ON enc.id = ob.encounter_id;

Patient Registration History Information

SELECT
     get_ods(epoc.organization_id) source_origanization,
     pat.nhs_number,
     get_concept_code(epoc.registration_status_concept_id) reg_status,
     get_concept_desc(epoc.registration_status_concept_id) reg_status_desc,
     get_concept_code(epoc.registration_type_concept_id) registration_type,
     get_concept_desc(epoc.registration_type_concept_id) registration_type_desc,
     DATE_FORMAT(epoc.date_registered,"%Y%m%d") date_registered,
     DATE_FORMAT(epoc.date_registered_end,"%Y%m%d") date_registered_end,
     epoc.id sequence_number,
     NULLIF(COALESCE(get_event_maxdate(6,epoc.id),0),0)  last_modified_date
FROM episode_of_care epoc JOIN patient pat ON epoc.patient_id = pat.id;

Patient Address Information

SELECT
     padr.id address_id,
     padr.address_line_1 address1,
     padr.address_line_2 address2,
     padr.address_line_3 address3,
     padr.address_line_4 address4,
     padr.city address5,
     padr.postcode postcode,
     parn.uprn,
     DATE_FORMAT(padr.start_date,"%Y%m%d") start_date,
     DATE_FORMAT(padr.end_date,"%Y%m%d") end_date,
     padr.lsoa_2001_code,
     padr.lsoa_2011_code,
     padr.msoa_2001_code,
     padr.msoa_2011_code,
     pat.nhs_number,
     parn.abp_address_number matched_address_1,
     parn.abp_address_street matched_address_2,
     parn.abp_address_locality matched_address_3,
     parn.abp_address_town matched_address_4,
     parn.abp_address_postcode matched_postcode,
     parn.classification,
     parn.abp_address_organization business_name,
     parn.match_date uprn_match_date,
     parn.status uprn_status,
     parn.latitude,
     parn.longitude,
     NULLIF(COALESCE(get_event_maxdate(20,padr.id),0),0)  last_modified_date
FROM patient_address padr JOIN patient pat ON padr.patient_id = pat.id
LEFT JOIN patient_address_match parn ON padr.id = parn.id;

Appointment Slot Information

SELECT
     appt.id slot_id,
     get_ods(appt.organization_id) source_origanization,
     appt.schedule_id session_id,
     DATE_FORMAT(appt.start_date,"%Y%m%d %T") start_time,
     appt.planned_duration planned_duration,
     appt.actual_duration actual_duration,
     get_concept_desc(appt.appointment_status_concept_id) status,
     DATE_FORMAT(appt.date_time_sent_in,"%Y%m%d %T") actual_start_time,
     appt.patient_wait wait_time,
     DATE_FORMAT(appt.cancelled_date,"%Y%m%d %T") date_cancelled,
     DATE_FORMAT(appt.date_time_left,"%Y%m%d %T") time_left,
     pat.nhs_number,
     NULLIF(COALESCE(get_event_maxdate(18,appt.id),0),0)  last_modified_date
FROM appointment appt JOIN patient pat ON appt.patient_id = pat.id;

Encounter Information

SELECT
     enc.id encounter_id,
     get_ods(enc.organization_id) source_organization,
     pat.nhs_number,
     DATE_FORMAT(enc.clinical_effective_date,"%Y%m%d %T") encounter_startdate,
     DATE_FORMAT(enc.end_date,"%Y%m%d %T") encounter_enddate,
     get_concept_desc(enc.non_core_concept_id) original_type,
     prac.role_desc hcp_role,
     enc.type encounter_type,
     get_ods(enc.service_provider_organization_id) event_organization,
     enc.appointment_id appointment_slot_id,
    NULLIF(GREATEST(COALESCE(get_event_maxdate(5,enc.id),0),
                    COALESCE(get_event_maxdate(13,prac.id),0)),0) last_modified_date
FROM encounter enc JOIN patient pat ON enc.patient_id = pat.id
LEFT JOIN practitioner prac ON prac.id = enc.practitioner_id;

Organization Information

SELECT
     org.id organization_id,
     org.ods_code,
     org.name organization_name,
     org.type_code,
     org.type_desc,
     org.postcode,
     org.parent_organization_id,
     NULLIF(COALESCE(get_event_maxdate(12,org.id),0),0)  last_modified_date
FROM organization org;

Appointment Session Information

SELECT
     sch.id session_id,
     sch.name session_name,
     get_ods(sch.organization_id) source_organization,
     prac.role_desc hpc_role,
     DATE_FORMAT(sch.start_date,"%Y%m%d %T:%f") start_time,
     sch.type session_type,
     sch.location location,
     NULLIF(GREATEST(COALESCE(get_event_maxdate(17,sch.id),0),
                     COALESCE(get_event_maxdate(13,prac.id),0)),0) last_modified_date
FROM schedule sch JOIN practitioner prac ON prac.id = sch.practitioner_id;