...

See algorithm outline for more details on the algorithm.

Notes:

• Relies on i2b2 data using the ACT ontology.

...

From your ACT database...

To install:

...

1. the

...

1. LU_CHARLSON

...

1. table.

...

1. Replace the prefix in the code pattern column if your data do not use ICD10CM: and ICD9CM:

...

To run:

1. Create a cohort filter

...

1. , 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.

These 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 

...

Customize the following statement and execute on your database to compute the loyalty cohort. EXEC [dbo].[usp_LoyaltyCohort_opt] @site='UKY', @lookbackYears=1, @demographic_facts=1, @gendered=0, @cohort_filter=@cfilter, @output=0  Note that steps 1-2 must be run in the same transaction.

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'

...

We are collecting outputs of this script to compare heuristics. If participating, contact us for access and then paste the output of step 3 into the Google sheet here: https://docs.google.com/spreadsheets/d/1ubuRt_ffVcZiQgUdOmeMXxgjOdfkpQe2FNFyt0u2Un4/edit?usp=sharing

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

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

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.