CODEWITHSUNDEEP
sqloracle-databaselag
Walt Reed
Walt Reed

Reputation: 1466

Oracle SQL Missing Years Calculate Last Value and Compound Interest

I have the following table in Oracle:

F1 F2 YEAR AMT
DC1 123 2021 1000
DC1 123 2022 1100
DC1 123 2023
DC1 123 2024
DC2 456 2021 5000
DC2 456 2022 6000
DC2 456 2023
DC2 456 2024

I want to calculate the missing years (YEAR 2023 and 2024) per grouping of F1 and F2 based on the AMT of the last available date (YEAR 2022) and multiply AMT by 2.1% compounding each year.

I've used the LAST_VALUE function

`(LAST VALUE(AMT IGNORE NULLS) OVER (PARTITION BY F1, F2 ORDER BY F1 F2 YEAR ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS NEXT_AMT`

to get the last values per grouping (1100 and 6000), but when I multiply by 2.1%, I don't get the compounding interest effect.

Desired Output:

F1 F2 YEAR AMT NEXT_AMT NEW_AMT
DC1 123 2021 1000 1000
DC1 123 2022 1100 1100
DC1 123 2023 1100 1123.1
DC1 123 2024 1100 1146.68
DC2 456 2021 5000 5000
DC2 456 2022 6000 6000
DC2 456 2023 6000 6126
DC2 456 2024 6000 6254.65

Upvotes: 1

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Calculate the ratio and then use arithmetic to do the multiplication:

with t as (
      select t.*,
             max(case when year = 2022 then amt end) over (partition by f1, f2) as year_2022,
             (max(case when year = 2022 then amt end) over (partition by f1, f2) /
              max(case when year = 2021 then amt end) over (partition by f1, f2)
             ) as ratio
      from mytable t
     )
select t.*,
       coalesce(amt,
                year_2022 * power(ratio, year - 2022)
               ) as new_amt
from t;

EDIT:

Oops, I misread the question. You actually have a fixed ratio for incrementing the amount. That is even easier:

with t as (
      select t.*,
             max(case when year = 2022 then amt end) over (partition by f1, f2) as year_2022
      from mytable t
     )
select t.*,
       coalesce(amt,
                year_2022 * power(1.021, year - 2022)
               ) as new_amt
from t;

I also realize that you might not want to hardcode 2022. So:

with t as (
      select t.*,
             last_value(case when amt is not null then year end ignore nulls) over (partition by f1, f2 order by year) as last_year,
             last_value(amt ignore nulls) over (partition by f1, f2 order by amt) as last_year_amt
      from mytable t
     )
select t.*,
       coalesce(amt,
                last_year_amt * power(1.021, year - last_year)
               ) as new_amt
from t;

Here is a db<>fiddle.

Upvotes: 3

Related Questions