SQL Query Dynamic Pivot with Dates (weeks)

Question

I'm trying to build a query that would give me the sum of sales per week. The only way to do it is through pivot table. However the problem I face is how do I make it dynamic? So instead of putting week numbers manually, can I get them to update automatically? In my query below where I've got weeks [11],[10],[9],[8],[7],[6]...- is there a way to have weeks [current week], [current week-1], [current week-2], [current week-3], [current week-4], [current week-5], [current week-6], etc?

SELECT * FROM (
SELECT ITEM,  sodetail.DESCRIPTION, QUANTITY,
YEAR (delivery_date) AS YR,
DATEPART (WEEK, delivery_date) AS WK,
DATEPART (WEEK, CURRENT_TIMESTAMP) AS CURR_WK

from sodetail
left join samaster on 
sodetail.ITEM = samaster.CODE
left join soheader on
sodetail.ORDER_NO = soheader.ORDER_NO

where
sodetail.STATUS <> '9'
AND ITEM NOT LIKE '/%'
AND DELIVERY_DATE >= CURRENT_TIMESTAMP - 600
AND ITEM = '49006'

)  t

PIVOT (
SUM (QUANTITY) 
FOR WK IN (

[11],[10],[9],[8],[7],[6],[5],[4],[3],[2],[1]
)

) AS PIVOT_TABLE

ORDER BY YR DESC

Sumit Tiwary · Answer 1 · 2020-03-11T07:49:31.223

You can follow this Query as sample. Add your columns and tablenames correctly as mentioned.

         DECLARE @sql NVARCHAR(MAX), @column NVARCHAR(MAX),@column0 NVARCHAR(MAX)
         BEGIN
                 SELECT @column = COALESCE(@column+',', '')+QUOTENAME(wk)
                 FROM
                 (
                     SELECT DISTINCT
                     Delivery_Date wk (Your column which you are putting in pivot)
                     FROM TableName
                  WHERE Conditions (Conditions should be specific in order to get distinct items. This you need to check clearly) 
                 ) AS x;
                 SET @column0 = ''''+REPLACE(@column, ',', ''',''')+'''';
                 SET @sql = '
    with cte as(
                         SELECT ColumnNames,Delivery_Date
         FROM TableName
         WHERE Conditions and Delivery_Date in ('+@column0+')
                     )      
                      SELECT ColumnNames
            '+@column+'
                     from cte
                     pivot
                             (
                              Sum(Quantity) FOR WK in ( '+@column+N')
                             )as PivotTable';
-- print @column ( in order to check which columns is going to be pivoted.

--select * from @sql  
--PRINT @sql; (in order to check query is correct)

--exec sp_executesql @sql( In order to execute)

As, per your query:-

    DECLARE @sql NVARCHAR(MAX), @column NVARCHAR(MAX), @column0 NVARCHAR(MAX),
    @mininterval int,@maxinterval int
    set @mininterval=0
    set @maxinterval=(whatever value you put in -ve)
    while(@mininterval>@maxinterval)
    BEGIN
                             SELECT @column = COALESCE(@column+',', '')+QUOTENAME(WK)
                             FROM
                             (         
    SELECT DISTINCT DATEPART(week,dateadd(week,@maxInterval,getdate())) AS WK      
    from (TableName and your where condition so that you get distinct week number)

 ) AS x
set @maxinterval=@maxinterval+1
end
 SET @column0 = ''''+REPLACE(@column, ',', ''',''')+'''';
                             SET @sql =' 
                with cte as(
                                    SELECT ITEM,sodetail.DESCRIPTION, QUANTITY,
                YEAR (delivery_date) AS YR,
                DATEPART (WEEK, delivery_date) AS WK,
                DATEPART (WEEK, CURRENT_TIMESTAMP) AS CURR_WK
                from sodetail
                left join samaster on 
                sodetail.ITEM = samaster.CODE
                left join soheader on
                sodetail.ORDER_NO = soheader.ORDER_NO
                where
                DATEPART (WEEK, delivery_date) in ('+@column0+')  
                AND sodetail.STATUS <> 9
                AND ITEM NOT LIKE ''''/%''''
                AND DELIVERY_DATE >= CURRENT_TIMESTAMP - 600
                AND ITEM = 49006
                                 )

                                  SELECT *,
                        '+@column+'
                                 from cte
                                 pivot
                                    (
                                     sum(Quantity) FOR Wk in ( '+@column+N')
                                    )as PivotTable';
        -- print @column
        --select * from @sql  
        --PRINT @sql;
        --EXEC SP_EXECUTESQL @SQL

I have used below query for getting week number.

select DATEPART(week,dateadd(week,-1,getdate()))

SQL Query Dynamic Pivot with Dates (weeks)

1 Answers1