Using the records along with getting the corresponding rows in GRP_PROD by joining on the ENRL_VERS.PROD_KEY = GRP_PROD.PROD_KEY, then get all the current rows for the GRP_PROD. Use the ENRL_VERS.MBR_COV_STRT_DT and ENRL_VERS.MBR_COV_END_DT in conjuction with the GRP_PROD.GRP_PROD_EFF_DT and GRP_PROD.GRP_PROD_TERM_DT to determine which months the member is enrolled and calculate COV_FOR_MBR_ENRL_MNTH_STRT_DT and COV_FOR_MBR_ENRL_MNTH_END_DT.
If the MBR_COV_STRT_DT and MBR_COV_END_DT is 1/2/2012 - 2/3/2012 and the GRP_PROD_EFF_DT and GRP_PROD_TERM_DT is 1/1/2012 - 1/19/2012 and 1/20/2012 - 12/31/2012.
For the MBR_ENRL_YR_MNTH_NBR 201201, the COV_FOR_ENRL_MNTH_STRT_DT is 1/2/2012 and the COV_FOR_ENRL_MNTH_END_DT is 1/19/2012
For the MBR_ENRL_YR_MNTH_NBR 201201, the COV_FOR_ENRL_MNTH_STRT_DT is 1/20/2012 and the COV_FOR_ENRL_MNTH_END_DT is 1/31/2012
For the MBR_ENRL_YR_MNTH_NBR 201202, the COV_FOR_ENRL_MNTH_STRT_DT is 2/1/2012 and the COV_FOR_ENRL_MNTH_END_DT is 2/3/2012
Below is the example I have tried. I need to achieve the expected target. If we want we can have multiple views/stage tables to achieve this.
AV_MBR_ENRL_VERS | ||||||
MBR_KEY | MBR_VERS_KEY | MBR_ENRL_VERS_KEY | MBR_COV_STRT_DT | MBR_COV_END_DT | GRP_PROD_VERS_KEY | |
1 | 100 | 1000 | 2016-01-02 | 2016-02-03 | 201 | |
AV_GRP_PROD | ||||||
GRP_PROD_VERS_KEY | GRP_PROD_KEY | GRP_PROD_EFF_DT | GRP_PROD_TERM_DT | |||
200 | 2 | 2016-01-01 | 2016-01-19 | |||
201 | 2 | 2016-01-20 | 2016-12-31 | |||
EXPECTED TARGET | ||||||
MBR_KEY | MBR_VERS_KEY | MBR_ENRL_VERS_KEY | COV_FOR_ENRL_MNTH_STRT_DT | COV_FOR_ENRL_MNTH_END_DT | MBR_ENRL_YR_MNTH_NBR | GRP_PROD_VERS_KEY |
1 | 100 | 1000 | 2016-01-02 | 2016-01-19 | 201601 | 200 |
1 | 100 | 1000 | 2016-01-20 | 2016-01-31 | 201601 | 201 |
1 | 100 | 1000 | 2016-02-01 | 2016-02-03 | 201602 | 201 |
Please help me to achieve the Target as per given above
Attached is the file for expected output