Oracle 11.2.0.4
Example SQL to explain my question:
https://livesql.oracle.com/apex/livesql/file/content_DMZ4GO3W9QL4BONQQB8I7NP5X.html The SQL I am working on is monthly data based on two categories (CATEGORY_A and CATEGORY_B). While we get values for CATEGORY_B, we do not always have values for CATEGORY_A. When there is no CATEGORY A value, though, the best we can do is infer the number based on CATEGORY_B's change ratio (if CATEGORY_B doubles from one month to the next, then CATEGORY_A would double). Everything is fine for a missing CATEGORY_A's value the first month after we have an actual value for CATEGORY_A (the LAG function can find the previous value), but subsequent months don't work since the LAG() only looks one month back.
As I see it, I need to either...
A) Be able to use the ADJUSTED_CATEGORY_A column to find the next ADJUSTED_CATEGORY_A value (kind of a recursive definition).
Eg. To find the third row's ADJUSTED_CATEGORY_A:
Use the 200 [ADJUSTED_CATEGORY_A] in the second row times the .5 [CATEGORY_B_CHANGE_RATIO] in the third row to create the value.
B) Use the last non-null CATEGORY_A value, but if I do that I need to be able to get the product of the CATEGORY_B_CHANGE_RATIO values between the current row and the row I find the last non-null value.
Eg. To find the third row's ADJUSTED_CATEGORY_A:
Use the 100 from CATEGORY_A's first row (the last non-null value) times the product of the CATEGORY_B_CHANGE_RATIO values between the second and third rows (2 times .5).
Hopefully this makes sense.
Thanks for providing a test case using LiveSQL!
But... surely you don't need to calculate the running product?
You can use the multiple between the last value of B where A had a value and B's current value.
You can find both of these using last_value with the "ignore nulls" clause. The value of A is just it's last value. The value of B is the last value of when A was non-null.
with rws as (
SELECT TO_DATE('01-JAN-2016') REPORT_MONTH, 100 CATEGORY_A, 200 CATEGORY_B FROM DUAL
UNION ALL
SELECT TO_DATE('01-FEB-2016') REPORT_MONTH, NULL CATEGORY_A, 600 CATEGORY_B FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAR-2016') REPORT_MONTH, NULL CATEGORY_A, 300 CATEGORY_B FROM DUAL
UNION ALL
SELECT TO_DATE('01-APR-2016') REPORT_MONTH, NULL CATEGORY_A, 100 CATEGORY_B FROM DUAL
UNION ALL
SELECT TO_DATE('01-MAY-2016') REPORT_MONTH, NULL CATEGORY_A, 200 CATEGORY_B FROM DUAL
UNION ALL
SELECT TO_DATE('01-JUN-2016') REPORT_MONTH, 200 CATEGORY_A, 100 CATEGORY_B FROM DUAL
UNION ALL
SELECT TO_DATE('01-JUL-2016') REPORT_MONTH, null CATEGORY_A, 200 CATEGORY_B FROM DUAL
)
select r.*,
last_value(category_a) ignore nulls over (order by report_month) lva,
last_value(case when category_a is not null then category_b end)
ignore nulls over (order by report_month) lvb,
last_value(category_a) ignore nulls over (order by report_month) *
(category_b) / last_value(case when category_a is not null then category_b end)
ignore nulls over (order by report_month) cat_a_calc
from rws r;
REPORT_MONTH CATEGORY_A CATEGORY_B LVA LVB CAT_A_CALC
01-JAN-2016 00:00:00 100 200 100 200 100
01-FEB-2016 00:00:00 600 100 200 300
01-MAR-2016 00:00:00 300 100 200 150
01-APR-2016 00:00:00 100 100 200 50
01-MAY-2016 00:00:00 200 100 200 100
01-JUN-2016 00:00:00 200 100 200 100 200
01-JUL-2016 00:00:00 200 200 100 400