Get only the current and one previous history for each ID

[smanas]

Hi I'm trying to get only the data for the current enrollment and end date and prior enrollment and end date for each individual. Eg is shown below. Once I get that I need to see if the prior End date is 6 months or more than the current enrollment date and flag as new else old. In the below example

1. I need only row 2 and 3 to appear for ID 1. 

2. IDs 1 and 3 should be flagged as New and ID 2 as old

ID	Enrollment Date	End Date
1	01/01/2021	06/01/2021
1	08/01/2021	01/01/2022
1	06/01/2022	01/01/2030
2	16/01/2022	12/01/2022
2	01/01/2023	10/01/2030
3	09/01/2022	8/01/2022
3	02/01/2023	11/10/2030

[vinieme12]

as below

temp:
Load *, if(addmonths(EnrollmentDate,-5)>=Peek('EndDate'),'new','old') as flag_
;
load ID,date(date#(EnrollmentDate,'MM/DD/YYYY')) as EnrollmentDate, date(date#(EndDate,'MM/DD/YYYY')) as EndDate inline [
ID,EnrollmentDate,EndDate
1,01/01/2021,06/01/2021
1,08/01/2021,01/01/2022
1,06/01/2022,01/01/2030
2,06/01/2022,12/01/2022
2,01/01/2023,10/01/2030
3,09/01/2022,8/01/2022
3,02/01/2023,11/10/2030
];

[smanas]

Hi @vinieme12  Can we please do this with the expression only not the script?Please advise thanks. Also, this is only sample data I have thousands of rows of data it would be hard to enter the enrollment date,end date for each ID. Any workaround?

[vinieme12]

Hi @smanas , can you explain the use case in detail

" hard to enter the enrollment date,end date for each ID."

[smanas]

Hi @vinieme12 - So, in your script you entered the dates, correct? is there a way to avoid that? and just use an expression that takes in the date? can we avoid the below?

1,01/01/2021,06/01/2021
1,08/01/2021,01/01/2022
1,06/01/2022,01/01/2030
2,06/01/2022,12/01/2022
2,01/01/2023,10/01/2030
3,09/01/2022,8/01/2022
3,02/01/2023,11/10/2030
];