Multi row: Determine active periods

[jnans]

I am stuck on a multi row agsin and hoping someone might have a suggestion.

I have a list of members who have various membership start dates and end dates and I am attempting to determine true join dates and leave dates. In theory, if a member has gaps in membership dated then they would have more than one Join Date and more than one leave date where as if they have multiple memberships but no gap in dates then they will have only one join date and one leave date. I have included what the sample data would look like and what my ideal output would look like.



Input Data

account_number Start_date End_Date
1000-100 2017-09-01 2017-10-01
1000-100 2017-10-01 2017-12-01
1000-100 2018-02-01 2018-06-01
1000-100 2018-06-01 2018-08-01
1000-100 2018-12-01 2019-11-19
1000-200 2018-01-01 2018-04-01
1000-200 2018-04-01 2019-12-01
1000-200 2019-12-01 2020-02-01


Ideal Output

account_number JoinDate LeaveDate
1000-100 2017-09-01 2017-12-01
1000-100 2018-02-01 2018-08-01
1000-100 2018-12-01 2019-11-01
1000-200 2018-01-01 2020-02-01

[grazitti_sapna · Accepted solution]

 Hi @jnans,

Please find the solution for your issue, hope it helps.

Cheers! 

[andyuttley · Accepted solution]

Hi @jnans 

I've used a multi row to create the necessary groups and just grouped from there:

Spoiler

Group=

if isnull([Row-1:Group])
then 1

elseif [Row-1:End_Date]=[Start_date]
then [Row-1:Group]

else [Row-1:Group]+1

endif

Also attached. Hope that helps!

Andy 

[jnans]

@grazitti_sapna & @andyuttley 

Thank You Both so much. I think with a couple minor tweaks this will work with my dataset.