Day 1 SCD View |
FORMULARY_STATUS_ID | SOURCE_PRODUCT_METRICS_AID | ACCOUNT_ID | PRODUCT_CATALOG_ID | PRODUCT_METRICS_DIMENSION_ID | START_DATE_ID | END_DATE_ID | START_DATE | END_DATE | ACTIVE_IND | COUNTRY_ID | PREV_FORMULARY_STATUS | CURR_FORMULARY_STATUS |
1 | sp1 | ac1 | pc1 | pm1 | a1 | b1 | 1/10/2016 | 15/10/2016 | 1 | 67 | Null | In Progress |
2 | sp1 | ac1 | pc1 | pm1 | a2 | b2 | 16/10/2016 | 31/10/2016 | 1 | 67 | In Progress | Approved |
3 | sp2 | ac2 | pc2 | pm2 | c1 | d1 | 1/11/2016 | 15/11/2016 | 1 | 67 | Null | Restricted |
4 | sp2 | ac2 | pc2 | pm2 | c2 | d2 | 16/11/2016 | 23/11/2016 | 1 | 67 | Restricted | Approved |
5 | sp3 | ac3 | pc3 | pm3 | c3 | d3 | 23/11/2016 | 1/1/9999 | 1 | 67 | Null | Regional Committee |
Day 2 SCD view |
FORMULARY_STATUS_ID | SOURCE_PRODUCT_METRICS_AID | ACCOUNT_ID | PRODUCT_CATALOG_ID | PRODUCT_METRICS_DIMENSION_ID | START_DATE_ID | END_DATE_ID | START_DATE | END_DATE | ACTIVE_IND | COUNTRY_ID | PREV_FORMULARY_STATUS | CURR_FORMULARY_STATUS |
1 | sp1 | ac1 | pc1 | pm1 | a1 | b1 | 1/10/2016 | 15/10/2016 | 1 | 67 | Null | In Progress |
2 | sp1 | ac1 | pc1 | pm1 | a2 | b2 | 16/10/2016 | 31/10/2016 | 1 | 67 | In Progress | Approved |
3 | sp2 | ac2 | pc2 | pm2 | c1 | d1 | 1/11/2016 | 15/11/2016 | 1 | 67 | Null | Restricted |
4 | sp2 | ac2 | pc2 | pm2 | c2 | d2 | 16/11/2016 | 23/11/2016 | 1 | 67 | Restricted | Approved |
5 | sp3 | ac3 | pc3 | pm3 | c3 | d3 | 23/11/2016 | 1/1/9999 | 1 | 67 | Null | Regional Committee |
6 | sp4 | ac4 | pc4 | pm4 | c4 | d4 | 23/11/2016 | 1/1/9999 | 1 | 67 | Null | Rejected |
7 | sp5 | ac5 | pc5 | pm5 | c5 | d5 | 23/11/2016 | 1/1/9999 | 1 | 67 | Null | Restricted |
output is:
sp4(6) and sp5(7) are new records in day 2 scd view
and sp3(5) is old record but status is not changed
Need your help finding solutions for this