Splitting of one to may rows using date range with the help other table's date range columns.

Database

Splitting of one to may rows using date range with the help other table's date range columns.

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

1 REPLY

Re: Splitting of one to may rows using date range with the help other table's date range columns.

Attached is the file for expected output