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.

...

• Relies on i2b2 data using the ACT 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 run:

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.

...

...

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.

...

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

...

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).

Parameter description:

• site: A short (3-character) identifier for your site.
• lookbackYears: A number of years for lookback. The original algorithm used 1 year, but we have found 3- or 5-years are more accurate, because some preventitive care like PSA and Pap Smears do not occurr every year.
• demographic_facts: Set to 1 if demographic facts (e.g., age, sex) are stored in the fact table (rather than patient_dimension).
• gendered: Set to 1 to create a summary table (and cutoffs) that do not include male-only facts for female patients in the denominator and vice-versa.
• filter_by_existing_cohort and cohort_filter: If the first is 1, specify a table variable of (PATIENT_NUM, COHORT_NAME) in the second parameter.
• output: If 1, pretty-print the loyalty_dev_summary percentages after the run.