Oracle SQL Loop to fill future dates

Have a question on this - my plan to fill the future weeks is to use previous years WoW movement (so I can get a full year forecast) - generally this works well for the business so am using this.

What I want to do is use the below as the actual revenue numbers:

And use the week on week movements from the year before to fill the future weeks so for example:

So I want to multiply the actual for week 4 by the week 5 multiple (1.125) to get a forecast value for week 5 (15,187.5), then for week 6 multiply 15,187.5 by 0.972 up to week 8:

Anyone got any ideas for code to do this?

Thanks

Solution 1:

You could use the model clause for this. There might be a more efficient/cleaner way, but assuming you have a table with year, week and revenue then this:

select year, week, revenue
from your_table
model
  dimension by (year, week)
  measures (revenue, 0 week_chg)
  rules upsert sequential order (
    week_chg[any, any] =
      revenue[cv(year) - 1, cv(week)]/revenue[cv(year) - 1, cv(week) - 1],
    revenue[any, any] =
      coalesce(
        revenue[cv(), cv()],
        round(revenue[cv(year), cv(week) - 1] * week_chg[cv(year), cv(week)], 2)
      )
  )

produces:

db<>fiddle

This applies two rules, in sequence. The first:

week_chg[any, any] =
      revenue[cv(year) - 1, cv(week)]/revenue[cv(year) - 1, cv(week) - 1]

works out the week change value from the previous year - the revenue from one year earlier and the same week, divided by the revenue from one year earlier and one week earlier.

Then

    revenue[any, any] =
      coalesce(
        revenue[cv(), cv()],
        round(revenue[cv(year), cv(week) - 1] * week_chg[cv(year), cv(week)], 2)

sets the revenue for the current dimension to either the real revenue value if there is; otherwise is multiples the revenue from the previous week (which, crucially, it may just have calculated itself) by the matching week change value. And then it rounds that value to two decimal places, which seems to be what you want.

(The last two results are very slightly different to your example because of rounding differences. If week_chg is rounded to 6 decimal places then the 2002 week 8 comes out as exactly 16875, but weeks 7 and 6 are then different.)

The fiddle has an intermediate result that shows both values, if you need them separately.

Solution 2:

Assuming you have the tables:

CREATE TABLE this_year (week, revenue) AS
SELECT 1, 10000 FROM DUAL UNION ALL
SELECT 2, 12000 FROM DUAL UNION ALL
SELECT 3, 15000 FROM DUAL UNION ALL
SELECT 4, 13500 FROM DUAL;

CREATE TABLE last_year (week, revenue) AS
SELECT 1,  5000 FROM DUAL UNION ALL
SELECT 2,  6000 FROM DUAL UNION ALL
SELECT 3,  7000 FROM DUAL UNION ALL
SELECT 4,  8000 FROM DUAL UNION ALL
SELECT 5,  9000 FROM DUAL UNION ALL
SELECT 6,  8750 FROM DUAL UNION ALL
SELECT 7,  9500 FROM DUAL UNION ALL
SELECT 8, 10000 FROM DUAL;

Then you can use a MODEL clause:

SELECT week,
       revenue
FROM   (
  SELECT ly.week AS week,
         ly.revenue AS ly_revenue,
         ty.revenue AS ty_revenue
  FROM   last_year ly
         LEFT OUTER JOIN this_year ty
         ON (ly.week = ty.week)
)
MODEL
  DIMENSION BY (week)
  MEASURES (
    ly_revenue,
    ty_revenue,
    0 AS ly_multiplier,
    0 AS revenue
  )
  RULES AUTOMATIC ORDER (
    revenue[1]      = COALESCE(
                        ty_revenue[1],
                        ly_revenue[1]
                      ),
    revenue[week>1] = COALESCE(
                        ty_revenue[cv()],
                        revenue[cv()-1] * ly_revenue[cv()]/ly_revenue[cv()-1]
                      )
  );

Which outputs:

WEEK	REVENUE
1	10000
2	12000
3	15000
4	13500
6	14765.625
5	15187.5
7	16031.25
8	16875

db<>fiddle here