All Forums Analytics
sagarmokashi 3 posts Joined 07/12
04 May 2016
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_PRO D_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|GR P_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
         
 
 

sjuolay 7 posts Joined 03/16
05 May 2016

I have two questions:

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

You must sign in to leave a comment.