Reposting -
If anyone can help me in this ,I will be really obliged .I have to submit this in next 15 hours.Thanks in advance.
I am having problem to create the desired output data.
I have to indentify the drugs which has been used in first 21 days and it will create initial line of treatment .but the below rules are applied to finalize the line of treatment.
- The start date will be first use of any drug irrespective of core or no core
- The end date will be last end date of last use of any core drug
- If any core drug from initial line of treatment is found within 60 days from the end date of initial line of treatment then the end of initial line of treatment would be updated to end date of this indentified core drug. Again i have to check for 60 days gap and so on.
- If a new core drug is identified (other than the core drug from initial line of treatment) before the end date of initial lot first lot will end one day before the start date of new core drug .Second line of treatment will be started from the start date of new core drug.
- If there is no core drug from initial lot of treatment within 60 days of the end date of initial line of treatment, then second line of treatment will be started and the same above rule will be applied to find third or subsequent lots.
- If there is any overlapping case like a core drug C1 is continue some days after starting of second line of treatment and it is again found any time after start of second line of treatment then the second line of treatment would contain the drugs from second line of treatment along with c1 .The end date will be the end of latest use of any core drug.
Data I have
patient_id drug_name drug_type start end
A11 N1 Non_core 23-Apr-14 21-May-14
A11 C1 Core 27-Apr-14 25-May-14
A11 C1 Core 3-May-14 31-May-14
A11 N1 Non_core 10-May-14 7-Jun-14
A11 C2 Core 11-May-14 8-Jun-14
A11 N2 Non_core 12-May-14 9-Jun-14
A11 C1 Core 10-Jul-14 7-Aug-14
A11 C1 Core 15-Jul-14 12-Aug-14
A11 C3 Core 29-Jul-14 26-Aug-14
A11 N4 Non_core 8-Sep-14 6-Oct-14
A11 N2 Non_core 9-Sep-14 7-Oct-14
A11 C1 Core 12-Sep-14 10-Oct-14
A11 C1 Core 15-Sep-14 13-Oct-14
A11 C1 Core 27-Sep-14 25-Oct-14
A11 C3 Core 1-Jan-15 29-Jan-15
A11 C1 Core 3-Jan-15 31-Jan-15
A11 C1 Core 5-Jan-15 2-Feb-15
A11 C1 Core 10-Jan-15 7-Feb-15
A11 N1 Non_core 15-Jan-15 12-Feb-15
A11 N2 Non_core 18-Jan-15 15-Feb-15
Data I want
patient_id drug start end lot
A11 C1+N1+C2+N2 23-Apr-14 28-Jul-14 1
A11 C3+C1+N4+N2 29-Jul-14 25-Oct-14 2
A11 C3+C1+N1+N2 1-Jan-15 15-Feb-15 3
Here lot represents line of treatment
In this data initially drugs found in first 21days are N1,c1,c1,n1,c2,n2 and the initial start date of lot 1 will be 4/23/2014 and initial end date will be the latest use of any core drug which is 6/8/2014
Next I need to check if there is any core drug from initial lot is available within 60 days of end of initial lot . c1 from lot 1 is there within 60 days .so the new end date will be updated to 8/7/2014.Again I have to look for 60 days criteria. i found again c1 is available within 60 days. So the new end date would be 8/12/2014.But as per the rule above there is a new core drug(other than the core drugs from initial lot) available before the end of initial line of treatment .so the first line of treatment will be end one day before the start date of newly indentified core drug. Then end of first lot is 7/28/2014.
Second line of treatment starts at 7/29/2014.the same above rules are applied here also to determine to end date .the drugs found in 21 days are C3, C1, N4, N2,C1.The initial end date will be 10/13/2014.There is an overlapping drug (C1) from lot 1 because latest use of C1 is 8/7/2014 which is after start of second lot and it is found again any time after start of lot 2.Then end date of second lot would be 10/25/2014.
As there is no drug found within 6o days so end of second lot, third lot will start at 1/1/2015.Same rule as above will be applied .
Any leads would be helpful.Thanks in advance.
