Site icon Fashions Style

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

admin

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.

Exit mobile version