Hi experts,
I have table1 in which data is like this.Here entity_id and eff_dt forms the primary key.
Table1
entity_id entity_n eff_dt end_dt
2000 ABC 1/1/2001 1/1/2002
2000 ABC 1/1/2002 1/1/2003
2000 DEF 1/1/2003 1/1/2004
2000 ABC 1/1/2004
2001 KLM 1/1/2006 1/1/2007
2001 KLM 1/1/2007 1/1/2008
2001 KLM 1/1/2008
2002 OPQ 1/1/2001
2003 PQR 1/1/1950 1/1/1951
2003 RST 1/1/1951
2004 MNO 1/1/1960 1/1/1961
2004 PQR 1/1/1961 1/1/1962
2004 MNO 1/1/1962 1/1/1963
2004 PQR 1/1/1963
I need the output like this.
Result
entity_id entity_n eff_dt end_dt
2000 ABC 1/1/2001 1/1/2003
2000 DEF 1/1/2003 1/1/2004
2000 ABC 1/1/2004
2001 KLM 1/1/2006
2002 OPQ 1/1/2001
2003 PQR 1/1/1950 1/1/1951
2003 RST 1/1/1951
2004 MNO 1/1/1960 1/1/1961
2004 PQR 1/1/1961 1/1/1962
2004 MNO 1/1/1962 1/1/1963
2004 PQR 1/1/1963
I tried to use Partition by functions to get the output but some or the other case is failing.
The logic behind the result is as follows
entity_id entity_n eff_dt end_dt
2000 ABC 1/1/2001 1/1/2002
2000 ABC 1/1/2002 1/1/2003
2000 DEF 1/1/2003 1/1/2004
2000 ABC 1/1/2004
For entity_id 2000 entity_n starts on 1/1/2001(eff_dt) and continues till 1/1/2003.The first two records are same.The eff_dt should be taken from first record and end_dt from second record.Again entity_n DEF starts from 1/1/2003 and ends on 1/1/2004. ABC starts again on 1/1/2004 and never ends.
So my out put should result in
entity_id entity_n eff_dt end_dt
2000 ABC 1/1/2001 1/1/2003
2000 DEF 1/1/2003 1/1/2004
2000 ABC 1/1/2004
I am attching this excel which has the sample data easy to load.
Thanks in advance.
Start Free Trial
