Hi all, still learning oracle analytical functions and hoping to get some help with the below issue
I have a dataset that looks like the below. i.e for the same Master_ID i have multiple pol_id's across different dates. I am creating a report and requirement is to create a 'new column 'that shows only the pol_id of the latest 'eff_dt' record for both rows.
Current Dataset:
with t as
( select '81WE6678' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2017' as Eff_dt from dual Union all
select '99WC2003' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2016' as Eff_dt from dual)
select pol_id, prev_pol_id, Master_GID, Eff_dt from t
| POL_ID | PREV_POL_ID | MASTER_GID | EFF_DT |
|---|
| 81WE6678 | 99WC2003 | 11060243 | 1/23/2017 |
| 99WC2003 | 99WC2003 | 11060243 | 1/23/2016 |
Expected Result is to add a new column called Latest_pol_Id and populate the older record(2016 eff_dt record) with the pol_id of the newer record(2017 eff_dt record)
| POL_ID | PREV_POL_ID | MASTER_GID | EFF_DT | LATEST_POL_ID |
|---|
| 81WE6678 | 99WC2003 | 11060243 | 1/23/2017 | 81WE6678 |
| 99WC2003 | 99WC2003 | 11060243 | 1/23/2016 | 81WE6678 |
with t as
( select '81WE6678' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2017' as Eff_dt, '81WE6678' as Latest_Pol_id from dual Union all
select '99WC2003' as pol_id, '99WC2003' as prev_pol_id, '11060243' as Master_GID, '1/23/2016' as Eff_dt, '81WE6678' as Latest_Pol_id from dual)
select pol_id, prev_pol_id, Master_GID, Eff_dt, Latest_Pol_id from t
https://livesql.oracle.com/apex/f?p=590:1:11557525977646:::::
Any ideas are appreciated.
Thanks a lot.
B