Hello,
I have a list of members that can be enrolled in a club. Some members stay continuously enrolled, while other stop and start. I'm trying to find those members who stopped during the year and then re-enrolled and how long the gap is. Each time a member re-enrolls, a new record is created. So some members will only have one record, but other could have 4 or 5 records. In the example below, I have members for just one year, but the records go back for three to four years. Any suggestions? Would an array work? I thought about transpose, but there are over 80,000 records. I tried a retain function to come up with one row per member, but I couldn't get it to work.
From the example below ,member 111 has 12 continuous months, but member 122 has a one month gap.
I'd appreciate any help.
ID Start_Date End_Date
111 01/01/2017 12/31/2017
122 01/01/2017 06/30/2017
122 08/01/2017 12/31/2017
133 01/01/2017 06/30/2017
133 07/01/2017 12/31/2017
144 1/1/2017 02/28/2017
144 04/01/2017 06/30/2017
144 08/01/2017 10/31/2017
144 11/01/2017 12/31/2017