Identifying an event in the past with a different start date for each person

[batulelec]

Hi!

I need some help with identifying if a patient has a prescription for a drug 6 months before the first fill date for the same drug. What is challenging here is that each patient will have a different first fill date and hence, the 6 month look back period will be different for each patient.

For example, for the year 2015 - one patient 1 has their first fill on January 5th while patient 2 has their first fill on March 30. For patient1 I will have to look back to July 5th while for patient 2 I will have to look back upto September 30. 

Currently my data is in this format - 

Patient ID                Medication               Fill date                

1                                  Med1                     01/23

1                                  Med2                     5/22

1                                  Med3                       ..

1                                  Med4                       ..

2                                  Med1                       ..

2                                  Med2                       ..

3                                  Med1                       ..

4                                  Med1                       ..                  

4                                  Med2                       ..

I can transpose the data and make it wide, but what I am struggling most with is identifying the different 6 month look back period for each patient from the fill date

Any help will be much appreciated!

Thank you!

[data_null__]

This is probably a job for INTNX to find the start date of fill-6months.  If you post representative data and desired output you will get better help.

[batulelec]

I already have the start date (Fill date) - I am not sure how to code so that the 6 month look back period is different for each patient. 

[data_null__]

Look at the documentation for INTNX you will find how to find a date 6 months in the past.

If you post some representative data we can show example.

[Reeza]

You don't need to change your data structure. 

You use what's called a self join. Join table to itself with the join condition being that the dates are within 6 months given ID and drug match. As mentioned INTNX will calculate the interval, but note that 6 months is not the same duration so you may want to define a fixed number of days rather than 6 months, ie 180 day? 

If you post sample input/output data for a code sample make sure to include the case where you have multiple dates for the same med, within the specified duration , ie patient 1 has med1 6 times in 6 months - monthly prescription. 

[Ksharp]

Show your sample data and output you want. It looks like SQL , Hash Table both can do that .