SQL Unpivot Rolling Date/Week Calculation

This is my original table. It has a forecast release date and 6 columns representing the forecast for 6 weeks followed by the original week date.

Forecast Week 1 = 10-09-2023

Forecast Week 2 = 17-09-2023

Forecast Week 3 = 24-09-2023

Forecast Week 4 = 01-10-2023

Forecast Week 5 = 08-10-2023

Forecast Week 6 = 15-10-2023

I have successfully unpivoted the columns to rows.

This is my query:

SELECT Forecast_Release_Date
    ,DATEADD(Week, 1, Forecast_Release_Date) AS Forecast_Week
    ,Adjusted_Forecast
FROM (
    SELECT TimeStamp AS Forecast_Release_Date
        ,Fcst_1
        ,Fcst_2
        ,Fcst_3
        ,Fcst_4
        ,Fcst_5
        ,Fcst_6
    FROM MyTable
    ) p
UNPIVOT(Adjusted_Forecast FOR [Week] IN (
            Fcst_1
            ,Fcst_2
            ,Fcst_3
            ,Fcst_4
            ,Fcst_5
            ,Fcst_6
            )) AS unpvt

But I can't seem to get the rolling weekly dates for the Forecast_Release_Week column i.e., first row should be 10-09, second = 17-09, and so on.

Note: This has to be dynamic based on the release date column and not hard-coded.

I tried following the code in Unpivot table and calculate field but it gave me the same results.

Any help is appreciated.

Thanks in advance.

P. S. - I am using Azure Synapse Data Warehouse SQL.

There are 2 variants below for different dbms:

---

For SQL Server (as the question was originally tagged)

There is another way to "unpivot" in SQL Server using cross apply and values, this produces one row for each entry in the values, and you can output as many columns as you need. I like to layout the values as you see below as it almost mimics what the result will look like:

SELECT
       SourceFileInsertDateTime AS Forecast_Release_Date
     , CrossApplied.*
FROM MyTable
CROSS APPLY (
    VALUES 
            (Adj_Fcst_1, dateadd(week,1,SourceFileInsertDateTime))
          , (Adj_Fcst_2, dateadd(week,2,SourceFileInsertDateTime))
          , (Adj_Fcst_3, dateadd(week,3,SourceFileInsertDateTime))
          , (Adj_Fcst_4, dateadd(week,4,SourceFileInsertDateTime))
          , (Adj_Fcst_5, dateadd(week,5,SourceFileInsertDateTime))
          , (Adj_Fcst_6, dateadd(week,6,SourceFileInsertDateTime))
        ) AS CrossApplied(Adjusted_Forecast, Forecast_Release_Week)

The technique is explained more fully here also see this answer

---

For use in Azure Synapse Data Warehouse SQL

SELECT
    SourceFileInsertDateTime AS Forecast_Release_Date,
    Adjusted_Forecast,
    Forecast_Release_Week
FROM
    (
        SELECT Adj_Fcst_1 AS Adjusted_Forecast, dateadd(week,1,SourceFileInsertDateTime) AS Forecast_Release_Week FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_2, dateadd(week,2,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_3, dateadd(week,3,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_4, dateadd(week,4,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_5, dateadd(week,5,SourceFileInsertDateTime) FROM MyTable
        UNION ALL
        SELECT Adj_Fcst_6, dateadd(week,6,SourceFileInsertDateTime) FROM MyTable
    ) AS T