Implements the loyalty cohort algorithm described in 

Klann, Jeffrey G., Darren W. Henderson, Michele Morris, Hossein Estiri, Griffin M. Weber, Shyam Visweswaran, and Shawn N. Murphy. 2023. “A Broadly Applicable Approach to Enrich Electronic-Health-Record Cohorts by Identifying Patients with Complete Data: A Multisite Evaluation.” Journal of the American Medical Informatics Association: JAMIA, August. https://doi.org/10.1093/jamia/ocad166.

See algorithm outline for more details on the algorithm.

• Algorithm Outline

Notes:

• Relies on i2b2 data using the ENACT ontology.
• If your data uses custom code prefixes (instead of ICD10CM: and ICD9CM:),  replace the prefix in the code pattern column in DT_LOYALTY_CHARLSON table.

How to Use the Loyalty Cohort Tool

1. Create a cohort filter, defining the patients on which to compute loyalty scores. The three columns are:
   • patient_num: patient_num from the i2b2 tables
   • cohort_name: a name for the cohort. You can optionally compute several cohorts separately, but specifying different values for this.
   • index_dt: a date which is a reference point in time at which to compute the loyalty score. It is suggested to select a common recent point in time or to choose each patient's most recent visit date, for example.
2. Run the USP_DT_LOYALTYCOHORT stored procedure with the following parameters.

Simple Example

The following examples create a cohort filter of the first 1000 rows in patient_dimension, and computes the loyalty cohort with an index date of 01 Feb 2021 and a one-year lookback.

MSSQL:

    DECLARE @cfilter UDT_DT_LOYALTY_COHORTFILTER;

     INSERT INTO @cfilter (PATIENT_NUM, COHORT_NAME, INDEX_DT)
     SELECT top 1000 psc.PATIENT_NUM, 'TEST', CONVERT(DATETIME, '20210201 ')
      from patient_dimension psc
     EXEC USP_DT_LOYALTYCOHORT @SITE='STE', @LOOKBACK_YEARS=1,  @demographic_facts=0, @GENDERED=0, @COHORT_FILTER=@cfilter, @output=0

  Note that the cohort filter must be created in the same transaction as the procedure execution.

Oracle:

     DELETE FROM DT_LOYALTY_COHORTFILTER WHERE COHORT_NAME='TEST'

     INSERT INTO DT_LOYALTY_COHORTFILTER (PATIENT_NUM, COHORT_NAME, INDEX_DT)
     SELECT psc.PATIENT_NUM, 'TEST2', to_date('2021-02-01', 'YYYY-MM-DD')
      from patient_dimension psc where rownum < 1000;
       BEGIN USP_DT_LOYALTYCOHORT('TEST', 1, 0, 0, 0); END;

1. Execute the following statement on your database to print the output that can be shared: select * from loyalty_dev_summary where Summary_Description='PercentOfSubjects'

2. This will create two tables on your db, DT_LOYALTY_RESULT (line level data with variables and score presented for each patient) and DT_LOYALTY_RESULT_SUMMARY (summary table).

Advanced Example

A cohort filter can be created from a patient set query in i2b2. Below is an example that looks up the cohort from a patient set query named "patient set test1" run by user "demouser".

MSSQL:

DECLARE @cfilter UDT_DT_LOYALTY_COHORTFILTER;

INSERT INTO @cfilter (PATIENT_NUM, COHORT_NAME, INDEX_DT)
select patient_num, 'EXAMPLE', CONVERT(DATETIME,'20200101') AS index_dt from
(select result.DESCRIPTION,pset.PATIENT_NUM from QT_QUERY_MASTER master
inner join QT_QUERY_INSTANCE instance on master.QUERY_MASTER_ID=instance.QUERY_MASTER_ID 
inner join QT_QUERY_RESULT_INSTANCE result on instance.QUERY_INSTANCE_ID=result.QUERY_INSTANCE_ID
inner join QT_PATIENT_SET_COLLECTION pset on result.RESULT_INSTANCE_ID=pset.RESULT_INSTANCE_ID
where master.name='patient set test1' and master.user_id='demouser') x

-- Edit for your site
EXEC [dbo].[USP_DT_LOYALTYCOHORT] @site='DEMO', @LOOKBACK_YEARS=3, @DEMOGRAPHIC_FACTS=0, @GENDERED=1, @COHORT_FILTER=@cfilter, @OUTPUT=0

Oracle:

1. Populate DT_LOYALTY_COHORTFILTER with patient cohort to be included:

INSERT INTO DT_LOYALTY_COHORTFILTER (PATIENT_NUM, COHORT_NAME, INDEX_DT)
SELECT psc.PATIENT_NUM, 'TEST', to_date('2021-02-01', 'YYYY-MM-DD')
FROM (
SELECT PSC.PATIENT_NUM
FROM QT_QUERY_MASTER QM
INNER JOIN QT_QUERY_INSTANCE QI ON QI.QUERY_MASTER_ID=QM.QUERY_MASTER_ID
INNER JOIN QT_QUERY_RESULT_INSTANCE QRI ON QRI.QUERY_INSTANCE_ID=RESULT.QUERY_INSTANCE_ID
INNER JOIN QT_PATIENT_SET_COLLECTION PSC ON PSC.RESULT_INSTANCE_ID=QRI.RESULT_INSTANCE_ID
WHERE QM.NAME='patient set test1' AND QM.USER_ID='demouser') X;

2. Execute USP_DT_LOYALTYCOHORT stored procedure

BEGIN USP_DT_LOYALTYCOHORT('TEST', 1, 0, 0, 0); END;
This will create two tables on your db, DT_LOYALTY_RESULT (line level data with variables and score presented for each patient) and DT_LOYALTY_RESULT_SUMMARY (summary table).

Loyalty Cohort Database Tables 

• DT_LOYALTY_PATHS: This table captures the specific concept paths related to loyalty features, with site-specific codes and optional comments. It is used to map ontology elements to binary variables for computing loyalty scores.

• DT_LOYALTY_CHARLSON: This table records Charlson Comorbidity Index categories, their respective weights, and code patterns to evaluate patient comorbidities. It supports the computation of Charlson scores as part of the loyalty evaluation.

• DT_LOYALTY_PSCOEFF: Contains field names and corresponding coefficients used for calculating predictive scores, essential for the regression equation used in loyalty score computation. It can be customized with locally-retrained weights (a mechanism for this has been developed but is not yet integrated into the Digital Twin package).

• DT_LOYALTY_RESULT_SUMMARY: Summarizes cohort data across various health metrics and tests, including gender-specific denominators, cutoff filters, and detailed descriptions of the cohort's health outcomes and probabilities. It provides an overview to validate cohort characteristics.

• DT_LOYALTY_RESULT: Detailed patient-level loyalty score data, capturing demographics, health screenings, and predictive scores for various health outcomes, including recent updates for tracking death dates. Critically, it includes the computed loyalty score.

• DT_LOYALTY_RESULT_CHARLSON: Similar to DT_LOYALTY_RESULT but focused on Charlson Comorbidity Index scores and detailed comorbidity categories for patients, including a variety of specific health conditions. It includes the calculated Charlson scores and 10-year survival probabilities.