How to count events before a different starting point for each row?-CodePudding

Suppose, I have a table with a list of patients and a date for their surgery.

ID_PATIENT  SURG_DATE
xxxxxxxxx1  07MAR2006:00:00:00
xxxxxxxxx2  11FEB2006:00:00:00
xxxxxxxxx3  14JAN2006:00:00:00
xxxxxxxxx4  01JAN2005:00:00:00

Suppose, I have a second table with a list of meds code which correspond to certain type of treatment (eg. treatment of diabetes)

MED_CODE
3484027
3484028

Now, I have a table with all the medications and the dates when they were dispensed.

ID_PATIENT  MED_CODE    DEL_DATE
xxxxxxxxx1  3484027 29DEC2005:00:00:00
xxxxxxxxx1  3484028 12JUN2005:00:00:00
xxxxxxxxx2  3484027 10JAN2005:00:00:00
xxxxxxxxx2  1234567 10MAR2005:00:00:00
xxxxxxxxx2  3484027 14APR2005:00:00:00
xxxxxxxxx3  3484027 12FEB2005:00:00:00
xxxxxxxxx3  3484028 14AUG2005:00:00:00
xxxxxxxxx3  3484027 17NOV2005:00:00:00
xxxxxxxxx4  3484027 17NOV2004:00:00:00
xxxxxxxxx4  3484027 20NOV2004:00:00:00
xxxxxxxxx4  3484027 13JAN2005:00:00:00

What I am trying to do is to create a new column in my patients' table which indicates if the patient had the medication at least three times within the year before the surgery. So in the example data only the third patient meet the requirements, because:

The first patient only had the treatment twice.
The second patient had three medications but one of them is not in the diabetes list.
The fourth patient had three medications but one of them is after the surgery.

Here is the code to produce the example data in PL/SQL:

create table PATIENTS
(
   ID_PATIENT varchar2(10)
,  SURG_DATE date
)
insert into PATIENTS values ('xxxxxxxxx1', to_date('20060307', 'yyyymmdd'))
insert into PATIENTS values ('xxxxxxxxx2', to_date('20060211', 'yyyymmdd'))
insert into PATIENTS values ('xxxxxxxxx3', to_date('20060114', 'yyyymmdd'))
insert into PATIENTS values ('xxxxxxxxx4', to_date('20050101', 'yyyymmdd'))

create table DIABETES_MEDS
(
    MED_CODE varchar2(10)
)

insert into DIABETES_MEDS values ('3484027')

insert into DIABETES_MEDS values ('3484028')

create table MEDS
(
   ID_PATIENT varchar2(10)
,  MED_CODE   varchar2(7)
,  DEL_DATE   date
)
insert into MEDS values ('xxxxxxxxx1', '3484027', to_date('20051229', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx1', '3484028', to_date('20050612', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx2', '3484027', to_date('20050110', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx2', '1234567', to_date('20050310', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx2', '3484027', to_date('20050414', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx3', '3484027', to_date('20050212', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx3', '3484028', to_date('20050814', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx3', '3484027', to_date('20051117', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx4', '3484027', to_date('20041117', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx4', '3484027', to_date('20041120', 'yyyymmdd'))
insert into MEDS values ('xxxxxxxxx4', '3484027', to_date('20050113', 'yyyymmdd'))

CodePudding user response：

This will give you the patients fulfilling the conditions:

SELECT COUNT(1), m.id_patient
  FROM meds m
     , patients p
 WHERE m.id_patient = p.id_patient
   AND m.med_code   IN (SELECT med_code FROM diabetes_meds)
   AND m.del_date   >= ADD_MONTHS(p.surg_date,-12)
   AND m.del_date   <= p.surg_date
 GROUP BY m.id_patient
HAVING COUNT(1) >= 3;

and once you create the new column you can fill it with a MERGE similar to this one:

MERGE INTO patients p
USING ( SELECT COUNT(1), m.id_patient
          FROM meds m
             , patients p
         WHERE m.id_patient = p.id_patient
           AND m.med_code   IN (SELECT med_code FROM diabetes_meds)
           AND m.del_date   >= ADD_MONTHS(p.surg_date,-12)
           AND m.del_date   <= p.surg_date
         GROUP BY m.id_patient
        HAVING COUNT(1) >= 3 ) meds
  ON (p.id_patient = meds.id_patient)
WHEN MATCHED THEN UPDATE SET <p.had_meds> = 'Y'; -- or whatever type of flag you want to use