Hi all,
I know there's quite a few examples of people filling in missing dates, but I can't figure out how to apply the solutions to my scenario.
The dataset I'm working has quarterly data, but I need to add some additional records to split the data around the issue_date anniversary and fill in the gaps.
How can I create extra rows like I have in my attached desired output? Note the pol_yr_begin and pol_yr_end columns aren't always accurate, but basically what I want is this.
Starting point
issue_date | Period_End | Period_Start | pol_yr_begin | pol_yr_end | type_of_record | start_date | end_date |
5/1/1985 | 6/30/2005 | 4/1/2005 | 5/1/2005 | 4/30/2006 | 5/1/2005 | 6/30/2005 | |
5/1/1985 | 6/30/2005 | 4/1/2005 | 5/1/2005 | 4/30/2006 | 7/1/2005 | 9/30/2005 | |
5/1/1985 | 9/30/2005 | 7/1/2005 | 5/1/2005 | 4/30/2006 | 10/1/2005 | 12/31/2005 | |
5/1/1985 | 12/31/2005 | 10/1/2005 | 5/1/2005 | 4/30/2006 | 10/1/2005 | 12/31/2005 | |
5/1/1985 | 3/31/2006 | 1/1/2006 | 5/1/2005 | 4/30/2006 | 1/1/2006 | 3/31/2006 | |
5/1/1985 | 6/30/2006 | 4/1/2006 | 5/1/2005 | 4/30/2006 | 4/1/2006 | 6/30/2006 | |
5/1/1985 | 9/30/2006 | 7/1/2006 | 5/1/2006 | 4/30/2007 | 7/1/2006 | 9/30/2006 | |
5/1/1985 | 12/31/2006 | 10/1/2006 | 5/1/2006 | 4/30/2007 | 10/1/2006 | 12/31/2006 | |
5/1/1985 | 3/31/2007 | 1/1/2007 | 5/1/2006 | 4/30/2007 | 1/1/2007 | 3/31/2007 | |
5/1/1985 | 6/30/2007 | 4/1/2007 | 5/1/2006 | 4/30/2007 | 4/1/2007 | 4/30/2007 | |
5/1/1985 | 9/30/2007 | 7/1/2007 | 5/1/2007 | 4/30/2008 | 7/1/2007 | 9/30/2007 | |
5/1/1985 | 12/31/2007 | 10/1/2007 | 5/1/2007 | 4/30/2008 | 10/1/2007 | 12/31/2007 | |
5/1/1985 | 3/31/2008 | 1/1/2008 | 5/1/2007 | 4/30/2008 | 1/1/2008 | 3/31/2008 | |
5/1/1985 | 6/30/2008 | 4/1/2008 | 5/1/2007 | 4/30/2008 | 4/1/2008 | 4/30/2008 | |
5/1/1985 | 8/31/2008 | 7/1/2008 | 5/1/2008 | 4/30/2009 | 7/1/2008 | 9/30/2008 |
Desired Output
issue_date | Period_End | Period_Start | pol_yr_begin | pol_yr_end | type_of_record | start_date | end_date | correct_start_date | correct_end_date |
5/1/1985 | 6/30/2005 | 4/1/2005 | 5/1/2005 | 4/30/2006 | 5/1/2005 | 6/30/2005 | 5/1/2005 | 6/30/2005 | |
5/1/1985 | 9/30/2005 | 7/1/2005 | 5/1/2005 | 4/30/2006 | 7/1/2005 | 9/30/2005 | 7/1/2005 | 9/30/2005 | |
5/1/1985 | 12/31/2005 | 10/1/2005 | 5/1/2005 | 4/30/2006 | 10/1/2005 | 12/31/2005 | 10/1/2005 | 12/31/2005 | |
5/1/1985 | 3/31/2006 | 1/1/2006 | 5/1/2005 | 4/30/2006 | 1/1/2006 | 3/31/2006 | 1/1/2006 | 3/31/2006 | |
5/1/1985 | 6/30/2006 | 4/1/2006 | 5/1/2005 | 4/30/2006 | 4/1/2006 | 6/30/2006 | 4/1/2006 | 4/30/2006 | |
5/1/2006 | 6/30/2006 | ||||||||
5/1/1985 | 9/30/2006 | 7/1/2006 | 5/1/2006 | 4/30/2007 | 7/1/2006 | 9/30/2006 | 7/1/2006 | 9/30/2006 | |
5/1/1985 | 12/31/2006 | 10/1/2006 | 5/1/2006 | 4/30/2007 | 10/1/2006 | 12/31/2006 | 10/1/2006 | 12/31/2006 | |
5/1/1985 | 3/31/2007 | 1/1/2007 | 5/1/2006 | 4/30/2007 | 1/1/2007 | 3/31/2007 | 1/1/2007 | 3/31/2007 | |
5/1/1985 | 6/30/2007 | 4/1/2007 | 5/1/2006 | 4/30/2007 | 4/1/2007 | 4/30/2007 | 4/1/2007 | 4/30/2007 | |
5/1/2007 | 6/30/2007 | ||||||||
5/1/1985 | 9/30/2007 | 7/1/2007 | 5/1/2007 | 4/30/2008 | 7/1/2007 | 9/30/2007 | 7/1/2007 | 9/30/2007 | |
5/1/1985 | 12/31/2007 | 10/1/2007 | 5/1/2007 | 4/30/2008 | 10/1/2007 | 12/31/2007 | 10/1/2007 | 12/31/2007 | |
5/1/1985 | 3/31/2008 | 1/1/2008 | 5/1/2007 | 4/30/2008 | 1/1/2008 | 3/31/2008 | 1/1/2008 | 3/31/2008 | |
5/1/1985 | 6/30/2008 | 4/1/2008 | 5/1/2007 | 4/30/2008 | 4/1/2008 | 4/30/2008 | 4/1/2008 | 4/30/2008 | |
5/1/2008 | 6/30/2008 | ||||||||
5/1/1985 | 8/31/2008 | 7/1/2008 | 5/1/2008 | 4/30/2009 | 7/1/2008 | 9/30/2008 | 7/1/2008 | 9/30/2008 |
Solved! Go to Solution.