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.

CodePudding user response：

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:

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().

CodePudding user response：

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.