This is hard to explain so I hope I can and that I've worded it correctly.
Need to calculate an expiration date:
row_nbr id purchase_date expiration date that needs to be calculated
1 1 03/29/2012 03/29/2013 - add 1 year
2 1 02/19/2013 03/29/2014 - since this member purchased their next membership BEFORE the 1st one expired, 2 years are added to the 1st purchase date
3 1 02/19/2014 03/29/2015 - since this member purchased their next membership BEFORE the 2nd one expired, 3 years are added to the 1st purchase date
4 1 02/17/2015 03/29/2016 - since this member purchased their next membership BEFORE the 3rd one expired, 4 years are added to the 1st purchase date
1 2 08/28/2013 08/28/2014 - add 1 year
1 3 08/28/2007 08/28/2008 - add 1 year
2 3 12/09/2008 12/09/2009 - since this member purchased their next membership AFTER the 1st one expired, 1 year is added to this 2nd purchase date
3 3 12/03/2009 12/09/2010 - since this member purchased their next membership BEFORE the 2nd one expired, 2 years is added to the 2nd purchase date
4 3 12/08/2010 12/09/2011 - since this member purchased their next membership BEFORE the 3rd one expired, 3 years are added to the 2nd purchase date
5 3 12/12/2011 12/12/2012 - since this member purchased their next membership AFTER the 4th one expired, 1 year is added to this 5th purchase date
6 3 12/17/2012 12/17/2013 - since this member purchased their next membership AFTER the 5th one expired, 1 year is added to this 6th purchase date
7 3 12/06/2013 12/17/2014 - since this member purchased their next membership BEFORE the 6th one expired, 2 years is added to the 6th purchase date
8 3 11/21/2014 12/17/2015 - since this member purchased their next membership BEFORE the 7th one expired, 3 years is added to the 6th purchase date
9 3 03/02/2016 03/02/2017 - since this member purchased their next membership AFTER the 8th one expired, 1 year is added to this 9th purchase date
1 4 08/25/2008 08/25/2009 - add 1 year
2 4 03/04/2010 03/04/2011 - since this member purchased their next membership AFTER the 1st one expired, 1 year is added to this 2nd purchase date
3 4 11/15/2010 03/04/2012 - since this member purchased their next membership BEFORE the 2nd one expired, 2 years is added to the 2nd purchase date
4 4 02/15/2012 03/04/2013 - since this member purchased their next membership BEFORE the 3rd one expired, 3 years is added to the 2nd purchase date
5 4 02/15/2013 03/04/2014 - since this member purchased their next membership BEFORE the 4th one expired, 4 years is added to the 2nd purchase date
6 4 03/26/2014 03/26/2015 - since this member purchased their next membership AFTER the 5th one expired, 1 year is added to this 6th purchase date
7 4 08/03/2015 08/03/2016 - since this member purchased their next membership AFTER the 6th one expired, 1 year is added to this 7th purchase date
Any help would be appreciated!
Need to calculate an expiration date:
row_nbr id purchase_date expiration date that needs to be calculated
1 1 03/29/2012 03/29/2013 - add 1 year
2 1 02/19/2013 03/29/2014 - since this member purchased their next membership BEFORE the 1st one expired, 2 years are added to the 1st purchase date
3 1 02/19/2014 03/29/2015 - since this member purchased their next membership BEFORE the 2nd one expired, 3 years are added to the 1st purchase date
4 1 02/17/2015 03/29/2016 - since this member purchased their next membership BEFORE the 3rd one expired, 4 years are added to the 1st purchase date
1 2 08/28/2013 08/28/2014 - add 1 year
1 3 08/28/2007 08/28/2008 - add 1 year
2 3 12/09/2008 12/09/2009 - since this member purchased their next membership AFTER the 1st one expired, 1 year is added to this 2nd purchase date
3 3 12/03/2009 12/09/2010 - since this member purchased their next membership BEFORE the 2nd one expired, 2 years is added to the 2nd purchase date
4 3 12/08/2010 12/09/2011 - since this member purchased their next membership BEFORE the 3rd one expired, 3 years are added to the 2nd purchase date
5 3 12/12/2011 12/12/2012 - since this member purchased their next membership AFTER the 4th one expired, 1 year is added to this 5th purchase date
6 3 12/17/2012 12/17/2013 - since this member purchased their next membership AFTER the 5th one expired, 1 year is added to this 6th purchase date
7 3 12/06/2013 12/17/2014 - since this member purchased their next membership BEFORE the 6th one expired, 2 years is added to the 6th purchase date
8 3 11/21/2014 12/17/2015 - since this member purchased their next membership BEFORE the 7th one expired, 3 years is added to the 6th purchase date
9 3 03/02/2016 03/02/2017 - since this member purchased their next membership AFTER the 8th one expired, 1 year is added to this 9th purchase date
1 4 08/25/2008 08/25/2009 - add 1 year
2 4 03/04/2010 03/04/2011 - since this member purchased their next membership AFTER the 1st one expired, 1 year is added to this 2nd purchase date
3 4 11/15/2010 03/04/2012 - since this member purchased their next membership BEFORE the 2nd one expired, 2 years is added to the 2nd purchase date
4 4 02/15/2012 03/04/2013 - since this member purchased their next membership BEFORE the 3rd one expired, 3 years is added to the 2nd purchase date
5 4 02/15/2013 03/04/2014 - since this member purchased their next membership BEFORE the 4th one expired, 4 years is added to the 2nd purchase date
6 4 03/26/2014 03/26/2015 - since this member purchased their next membership AFTER the 5th one expired, 1 year is added to this 6th purchase date
7 4 08/03/2015 08/03/2016 - since this member purchased their next membership AFTER the 6th one expired, 1 year is added to this 7th purchase date
Any help would be appreciated!