Calculate new rows (or columns) based previously calculated rows (or columns) in same select statement

user_10647743 0
Viewed 169

I am trying to calculate the yearly expected sales volumes based on yearly sales growth expectations. In one table I have the actual sales volume:

create table #Sales (
    ProductId int,
    Year int,
    GrowthRate float
    )

insert into #Sales
values 
    (1, 2021, 1000),
    (2, 2021, 5000)

and in another table I have the yearly growth rates:

create table #GrowthRates (
    ProductId int,
    Year int,
    GrowthRate float
    )

insert into #GrowthRates
values
    (1, 2022, 0.02),
    (1, 2023, 0.04),
    (1, 2024, 0.03),
    (1, 2025, 0.05),
    (2, 2022, 0.10),
    (2, 2023, 0.12),
    (2, 2024, 0.05),
    (2, 2025, 0.09)

Is there a way to calculate Sales2022 = Sales2021 * (1+GrowthRate2022), and calculate another row Sales 2023 = Sales2022(from previously) * (1+GrowthRate2023) and so on and so forth until 2025 all in one go within on select statement. Perhaps a loop or something? I am trying to avoid having to create a intermediary temp table for each year.

Final output should be something like this table. I can pivot the years to columns, if that would help.

sql-server sql loops
2 Answers

You can use a recursive cte to calculate this based on the previous row:

WITH recursive_cte AS
(
    -- Anchor member
    SELECT s.productid, s.year, s.growthrate from #sales s
    UNION ALL
    -- Recursive member that references expression_name.
    SELECT 
        g.productid, 
        g.year,
        r.growthrate * (1 + g.growthrate) as growthrate
    FROM #growthrates g
        INNER JOIN recursive_cte r 
            ON r.productid = g.productid AND r.year = g.year - 1
)
-- references expression name
  SELECT *
    FROM recursive_cte
ORDER BY productid, year

Output:

productid year growthrate
1 2021 1000
1 2022 1020
1 2023 1060.8
1 2024 1092.624
1 2025 1147.2552
2 2021 5000
2 2022 5500
2 2023 6160
2 2024 6468
2 2025 7050.12

If you don't want the fractions, add a rounding function.

See db<>fiddle for both the above in action as well as an example that uses round().

user_140157370

I'm adding @lptr's comment-only-answer as a proper answer here as well, since it is a nice alternative and leads to the same result.

This solution creates a running multiplication product by using logarithms in conjunction with a windowing function:

  select g.productid, 
         g.year,
         floor(s.growthrate*exp(sum(log(1+g.growthrate)) over(partition by g.productid order by g.year)))
    from #Sales as s
    join #GrowthRates as g on s.productid = g.productid 
                          and s.year < g.year
order by productid,year

See this db<>fiddle for @lptr's original solution.

user_140157370
Related