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.