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

Analytics

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

Hello every one, 

I want to split the rows of one table based on date range of another table..

If end_dt of 1st table is greater than end_dt of 2nd table then end_dt should be the second table's end date and then split the records monthwise

as given in the below example.

e.g. I have 2 tables as below

Table 1

ENROLLMENT_VERSION

MBR_KEY|MBR_VERS_KEY|EMBR_ENRL_VERS_KEY|STRT_DT       |END_DT      |GRP_PROD_VERS_KEY

            1|100                   |1000                            |2016-01-02 |2016-03-31| 201

            2|101                   |1001                            |2016-01-01 |2016-03-31| 202

            3|102                   |1002                            |2016-01-15 |2016-03-31| 203

           4|103                    |1003                            |2016-01-01|2016-03-14 | 204

Table 2

GPR_PROD

GPR_PROD_VERS_KEY|GPR_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

                          202  |                      3|2016-01-01           |2016-12-31

                          203 |                       4|2016-01-01           |2016-12-31

                          204 |                       5|2016-01-01           |2016-12-31

Expected result

MBR_KEY|MBR_VERS_KEY|MBR_COV_STRT_DT|MBR_COV_END_DT|GRP_PROD_VERS_KEY

            1|100                   |2016-01-02           |2016-01-19           |200

            1|100                   |2016-01-20           |2016-01-31           |201

            1|100                   |2016-02-01           |2016-02-29           |201

            1|100                   |2016-03-01           |2016-03-31           |201

1 REPLY
Enthusiast

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

I have two questions:

1. which are the two variables to join the two variables?
2. what do you mean by splitting the rows?