SQL Unpivot Rolling Date/Week Calculation

Question

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_Release_Date	Adj_Fcst_1	Adj_Fcst_2	Adj_Fcst_3	Adj_Fcst_4	Adj_Fcst_5	Adj_Fcst_6
03-09-2023	299	312	309	248	282	270

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.

Forecast_Release_Date	Forecast_Release_Week	Adjusted_Forecast
03-09-2023	10-09-2023	299
03-09-2023	10-09-2023	312
03-09-2023	10-09-2023	309
03-09-2023	10-09-2023	248
03-09-2023	10-09-2023	282
03-09-2023	10-09-2023	270

This is my query:

SELECT Forecast_Release_Date
    ,DATEADD(Week, 1, Forecast_Release_Date) AS Forecast_Week
    ,Adjusted_Forecast
FROM (
    SELECT SourceFileInsertDateTime AS Forecast_Release_Date
        ,Adj_Fcst_1
        ,Adj_Fcst_2
        ,Adj_Fcst_3
        ,Adj_Fcst_4
        ,Adj_Fcst_5
        ,Adj_Fcst_6
    FROM MyTable
    ) p
UNPIVOT(Adjusted_Forecast FOR [Week] IN (
            Adj_Fcst_1
            ,Adj_Fcst_2
            ,Adj_Fcst_3
            ,Adj_Fcst_4
            ,Adj_Fcst_5
            ,Adj_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.

Answer 1

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