T-SQL new patient with prescription during clincial trial period

Question

Hi,

I am trying to solve an interesting problem, so far couldn't get it straight. (Attached a temp table script of the example cases shared below)

I have a table, that has multiple orders of Tylenol medications taken by a patient with medications start and end dates. A patient might have multiple medication orders. There is a clinical trial initiated between '01-01-2017' and '12-31-2019'. Now i wanted to find patients with new prescription (med_start date) is during the trial period. Here new prescription is anyone who have med_start date in clinical trial period and not taken above medication 18 months prior to first instance (med_start date) of medication during the clinical trial.

Below is the table structure (Columns A to E), just for explanation i added two columns F and G that is clinical trial dates.

For instance,

First patient 125 he has 2 instances of medication orders (with med_start date) during study period (highlighted in yellow). However, there is one instance (highlighted in blue) the med_end date falls under study period. Now he is not new patient, i need to exclude him.

With second patient 388, he has 1 instance of medication orders (with med_start date) during study period (highlighted in green). However, there is many instance (highlighted in dark orange) the med_start date is within 18 months prior to first instance start date i.e, '01-04-2017'. Also, one instance the med_end date falls under study period (highlighted in grey)
Now he is not new patient, i need to exclude him.

Next, with patient 514 he has 2 instances of medication orders (with med_start date) during study period (highlighted in green). And he has no medication prior 18 months to first instance, however he has old order which is outside of 18 months prior window (highlighted in red text),so he is considered clearly a new patient. So i need to include this patient.

drop table #medication_orders

create table #medication_orders (patientid int, medication_ord_id int , med_name varchar(10), med_start datetime, med_end datetime)

insert into #medication_orders values (125 , 2722183 , 'tylenol' , '2013-11-07' , '2014-04-10');
insert into #medication_orders values (125 , 11948151 , 'tylenol' , '2014-04-10' , '2019-09-23');
insert into #medication_orders values (125 , 111848465 , 'tylenol' , '2019-09-23' , '2019-09-26');
insert into #medication_orders values (125 , 111848471 , 'tylenol' , '2019-09-26' , '2020-09-21');
insert into #medication_orders values (125 , 120266136 , 'tylenol' , '2020-09-21' , '2021-08-24');
insert into #medication_orders values (125 , 141965985 , 'tylenol' , '2021-08-24' , '2021-08-24');
insert into #medication_orders values (388 , 20888520 , 'tylenol' , '2014-06-28' , '2015-08-13');
insert into #medication_orders values (388 , 20888545 , 'tylenol' , '2015-08-13' , '2016-02-23');
insert into #medication_orders values (388 , 47083664 , 'tylenol' , '2016-01-11' , '2016-01-13');
insert into #medication_orders values (388 , 47257413 , 'tylenol' , '2016-02-23' , '2016-03-10');
insert into #medication_orders values (388 , 47257429 , 'tylenol' , '2016-03-10' , '2016-09-01');
insert into #medication_orders values (388 , 57715171 , 'tylenol' , '2016-09-01' , '2016-10-03');
insert into #medication_orders values (388 , 57715175 , 'tylenol' , '2016-10-03' , '2017-01-04');
insert into #medication_orders values (388 , 63555167 , 'tylenol' , '2017-01-04' , '2018-01-04');
insert into #medication_orders values (514 , 100691229 , 'tylenol' , '2014-03-06' , '2014-12-14');
insert into #medication_orders values (514 , 102551421 , 'tylenol' , '2019-04-03' , '2019-10-02');
insert into #medication_orders values (514 , 112352781 , 'tylenol' , '2019-10-02' , '2020-03-06');
insert into #medication_orders values (514 , 120691229 , 'tylenol' , '2020-03-06' , '2020-03-06');
insert into #medication_orders values (514 , 120691230 , 'tylenol' , '2020-03-06' , '2020-03-06');
insert into #medication_orders values (514 , 120691231 , 'tylenol' , '2020-03-06' , '2020-03-11');
insert into #medication_orders values (514 , 120691232 , 'tylenol' , '2020-03-11' , '2020-10-08');
insert into #medication_orders values (514 , 129639214 , 'tylenol' , '2020-10-08' , '2021-06-14');
insert into #medication_orders values (514 , 147131076 , 'tylenol' , '2021-08-02' , '2022-08-02');
insert into #medication_orders values (805 , 11535921 , 'tylenol' , '2013-10-17' , '2014-10-23');
insert into #medication_orders values (805 , 26525214 , 'tylenol' , '2014-10-23' , '2014-10-23');
insert into #medication_orders values (805 , 26525606 , 'tylenol' , '2014-10-23' , '2017-10-27');
insert into #medication_orders values (805 , 71641739 , 'tylenol' , '2017-06-28' , '2017-06-28');
insert into #medication_orders values (805 , 71779426 , 'tylenol' , '2017-10-27' , '2017-10-30');
insert into #medication_orders values (805 , 71779428 , 'tylenol' , '2017-10-30' , '2018-03-23');
insert into #medication_orders values (805 , 80210196 , 'tylenol' , '2017-12-23' , '2017-12-28');
insert into #medication_orders values (805 , 84391648 , 'tylenol' , '2018-03-23' , '2019-04-05');
insert into #medication_orders values (805 , 97438539 , 'tylenol' , '2018-12-19' , '2018-12-27');
insert into #medication_orders values (805 , 97576290 , 'tylenol' , '2019-04-05' , '2019-11-13');
insert into #medication_orders values (805 , 111123565 , 'tylenol' , '2019-09-15' , '2019-09-10');
insert into #medication_orders values (805 , 112434892 , 'tylenol' , '2019-11-07' , '2019-11-06');
insert into #medication_orders values (805 , 114434885 , 'tylenol' , '2019-11-09' , '2019-11-08');
insert into #medication_orders values (805 , 114649927 , 'tylenol' , '2019-11-13' , '2020-09-09');
insert into #medication_orders values (805 , 122295459 , 'tylenol' , '2020-09-09' , '2021-01-11');
insert into #medication_orders values (805 , 135207227 , 'tylenol' , '2021-01-11' , '2021-04-13');
insert into #medication_orders values (805 , 140783678 , 'tylenol' , '2021-04-13' , '2021-07-13');
insert into #medication_orders values (805 , 149052088 , 'tylenol' , '2021-07-13' , '2021-07-14');
insert into #medication_orders values (805 , 149052091 , 'tylenol' , '2021-07-14' , '2021-07-14');

Select *, '01-01-2017' as study_start_date, '12-31-2019' as study_end_date
from #medication_orders
order by 1,4

Finally, with patient 805 has 11 instances of medication orders (with med_start date) during study period (highlighted in yellow). And he has one medication prior 18 months to first instance (highlighted in purple), in addition he has old order which is outside of 18 months prior window (highlighted in red text),so he is not a new patient. So i need to exclude this patient.

So, basically out of above 4 use cases only one patient 514 is meeting the criteria of actual new patient.

Any help how to solve this will be greatly appreciated.

Thanks

Accepted Answer

If I get this right:

; WITH patients AS (
   SELECT DISTINCT patientid FROM #medication_orders WHERE med_name = 'tylenol'
)
SELECT p.patientid
FROM   patients p
WHERE  EXISTS (SELECT *
               FROM   #medication_orders mo
               WHERE  mo.patientid = p.patientid
                 AND  mo.med_name = 'tylenol'
                 AND  mo.med_start BETWEEN '20170101' AND '20191231')
  AND  NOT EXISTS (SELECT *
                   FROM   #medication_orders mo
                   WHERE  mo.patientid = p.patientid
                     AND  mo.med_name = 'tylenol'
                     AND  mo.med_start < '20170101'
                     AND  mo.med_end >= '20150701')

Answer

I am not sure that your modification meet the requirements, but you know more about this than I do. But here is a query which I think addresses the shortcoming of my original query:

; WITH patients AS (
   SELECT patientid, MIN(med_start) AS first_instance
   FROM   #medication_orders 
   WHERE  med_name = 'tylenol'
     AND  med_start >= '20170101'
   GROUP  BY patientid
)
SELECT p.patientid
FROM   patients p
WHERE  EXISTS (SELECT *
               FROM   #medication_orders mo
               WHERE  mo.patientid = p.patientid
                 AND  mo.med_name = 'tylenol'
                 AND  mo.med_start BETWEEN '20170101' AND '20191231')
  AND  NOT EXISTS (SELECT *
                   FROM   #medication_orders mo
                   WHERE  mo.patientid = p.patientid
                     AND  mo.med_name = 'tylenol'
                     AND  mo.med_start < p.first_instance
                     AND  mo.med_end >= dateadd(month, -18, p.first_instance))

Share via

T-SQL new patient with prescription during clincial trial period

1 additional answer