Hi All, could use some help with the below requirement. I tried using the standard syntax of last_value() function but cant seem to make it work.
Oracle Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Background: The data below shows the current scenario where premium is missing for latest records. In these cases the Solution recommended by customer is to use last valid value and carry it forward to subsequent records.
Current dataset: Grain of the data is Master_GID and Eff_dt
with t as ( select '81WE6678' as pol_id, 'ca' as state, '11060243' as Master_GID, '1/23/2017' as Eff_dt , 700 as premium from dual Union all
select '99WC2003' as pol_id, 'ny' as state, '11060243' as Master_GID, '1/23/2018' as Eff_dt, 800 as premium from dual Union all
select '99WC2045' as pol_id, 'nj' as state, '11060243' as Master_GID, '1/23/2018' as Eff_dt, 900 as premium from dual Union all
select '99WC2045' as pol_id, 'tx' as state, '11060243' as Master_GID, '1/23/2019' as Eff_dt, null as premium from dual Union all
select '99WC2045' as pol_id, 'mo' as state, '11060243' as Master_GID, '1/23/2020' as Eff_dt, null as premium from dual
) select pol_id, state, Master_GID, Eff_dt, premium from t
Solution: In the above scenario data is missing for the column 'premium' for years 2019 and 2020. In these cases requirement is to take data from 2018 year and carry forward to future years. as shown in 'premium_fix' column values.
with t as ( select '81WE6678' as pol_id, 'ca' as state, '11060243' as Master_GID, '1/23/2017' as Eff_dt , 700 as premium, 700 as premium_fix from dual Union all
select '99WC2003' as pol_id, 'ny' as state, '11060243' as Master_GID, '1/23/2018' as Eff_dt, 800 as premium, 800 as premium_fix from dual Union all
select '99WC2045' as pol_id, 'nj' as state, '11060243' as Master_GID, '1/23/2018' as Eff_dt, 900 as premium, 900 as premium_fix from dual Union all
select '99WC2045' as pol_id, 'tx' as state, '11060243' as Master_GID, '1/23/2019' as Eff_dt, null as premium, 900 as premium_fix from dual Union all
select '99WC2045' as pol_id, 'mo' as state, '11060243' as Master_GID, '1/23/2020' as Eff_dt, null as premium,900 as premium_fix from dual
) select pol_id, state, Master_GID, Eff_dt, premium, premium_fix from t
I have to look at Master_GID and Eff_dt combination. I tried this but not working:
select pol_id, state, Master_GID, Eff_dt, premium,
last_VALUE ( premium)
IGNORE NULLS OVER ( PARTITION BY Master_GID, Eff_dt ORDER BY Eff_dt ASC rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS premium_fix from t
Any pointers are greatly appreciated.