Search code examples
sqlpivotazure-synapseunpivotcross-apply

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_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.


Solution

  • 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