I am dealing with health care enrollment data right now, facing the following problem. Raw data looks like ID stard_date end_date P1 A B P1 B+1 C P1 C+180 D P2 E F P2 F+1 G P3 H I The expected output is like ID stard_date end_date P1 A C P1 C+180 D P2 E G P3 H I The criteria of difining consecutive coverage or not could be parameterized, for example, in above example, if there is only 1 day gap, we consider it as consecutive, if the gap is large enough( 180 days), we don't concatenate them( see P1). In addition, in order to make the ID as unique key, I am thinking about to change the ID as "ID+start date" ID stard_date end_date P1+A A C P1 +C+180 C+180 D P2 +E E G P3 +H H I Thanks in advance
... View more