Dear All,
Can I please seek your help on this by using sql?
I have a dataset containing id, date_1, date_2 and indication.
I would like to derive get, keep, date_3 and group.
The rationale is to flag keep=Y for the all the data rows with the earliest dates of both date_1 and date_2 for all the ids
If the data rows of keep=Y having both the same month and year i.e. id=1, then date_3=date_2 and group=Gen
If the data rows of keep=Y having different month and year i.e. id=2, then group=Indication.
Thanking you in advance.
id date_1 date_2 indication keep date_3 group
1 01-02-2020 20-02-2020 Y
1 01-02-2018 03-03-2018 Y
1 01-02-1990 20-02-1990 N Y 20-02-1990 Gen
1 01-02-1990 20-02-1990 Y Y 20-02-1990 Gen
1 01-02-1990 20-02-1990 Y Y 20-02-1990 Gen
1 01-02-1990 20-02-1990 N Y 20-02-1990 Gen
2 01-06-2009 25-08-2010 Y Y Y
2 01-06-2009 25-08-2010 N Y N
2 01-06-2009 25-08-2010 N Y N
2 01-06-2009 25-08-2010 Y Y Y
2 01-05-2018 08-06-2019 Y
2 01-05-2018 04-12-2019 Y
... View more