PL/SQL query to pull active date for a value
I need the effective dates (start and end) of marital status changes in sequential order, without duplicate rows over the same time frame. (Per_all_people_f table only)
For example below, I only need the items that are in bold. I am very new to pl/sql and cannot figure out how to do this.
When I do this in sql with Min date and max date; the 1st and 2nd blocks are correct, the 3rd block has wrong end date and 4th block is entirely missing as the 'M' is already counted for in block 1 even though it occurred after other status changes.
Example of the rows and what I need in BOLD below:
So no gaps in time and it captures the effective date range for that particular marital status; I need to get:
1st block 'S' 10/23/2000 - 4/12/2004
2nd block 'M' 4/13/2004 - 10/1/2006
3rd block 'D' 10/2/2006 - 5/23/2007
4th block 'M' 5/24/2007 - 12/31/4712
Actual data in table I do get on a query with no restrictions:
490 10/23/2000 4/12/2004 0 US S F
490 4/13/2004 10/1/2006 0 US M F
490 10/2/2006 2/12/2007 0 US D F
490 2/13/2007 5/23/2007 0 US D F
490 5/24/2007 10/7/2010 0 US M F
490 10/8/2010 11/15/2012 0 US M F
490 11/16/2012 12/31/4712 0 US M F