I have 3 columns. ID, Effective Date and Term Date in the source
I have 4 columns ID, Effective Date, Term Date and Earliest Continuous Date in the target
ID, Effective date and term date will remain same between source and target, but for Earliest Continuous Date, I need to populate min effective date which have continuous eff and term date.
For example. I have uploaded file with expected target data. If you notice continuation of Effective Date and Term Date
record 1, 2 & 3 are continuous from 02/01/2016 to 01/01/2017 hence earliest continuous date for these 3 records is 02/01/2016
record 4 has gap of 5 months and 06/01/2017 to 06/01/2017, hence earliest continuous date for the record is 06/01/2017
records 5,6,7,8 are continuous from 09/01/2017 to 02/01/2019, hence earliest continuous date for these 3 record is 09/01/2017.
I have tried in google to find solution, but not getting it. Can somebody please help me to solve this?
ID,EFFECTIVE_DATE, TERM_DATE, EFFECTIVE_CONTINUOUS_DATE