0

I have created dynamic pivot sql that works well, but i still have a simple problem - how to change this line in dynamic part of sql WHERE DUE_DATE BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) so that I can manage dates between using variables (f.e.: @startdate and @enddate). Where i should add and declare variables that will works in this place. Тhe goal is for the user to set the counting period by himself. Help. Any suggestions?

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @selectCols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

DECLARE @DATANUO DATETIME

SET @DATANUO = '2021-12-01' 


SELECT  @selectCols = STUFF((SELECT ', ISNULL(' + QUOTENAME(DATEPART(DAY,DUE_DATE)) + ', 0) AS ' + QUOTENAME(DATEPART(DAY,DUE_DATE))
                    FROM   [DB].[sbo].[SALES]
                    WHERE DUE_DATE BETWEEN @DATANUO AND EOMONTH(@DATANUO)
                    GROUP BY DUE_DATE
                    ORDER BY DUE_DATE
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(DATEPART(DAY,DUE_DATE)) 
                    FROM [DB].[sbo].[SALES]
                    WHERE DUE_DATE BETWEEN @DATANUO AND EOMONTH(@DATANUO)
                    GROUP BY DUE_DATE
                    ORDER BY DUE_DATE
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = '  SELECT Branch,' + @selectCols + ' FROM 
                (
                SELECT Branch AS Branch,    DATEPART(DAY,DUE_DATE) AS Diena,     COUNT(*) AS Sales
  FROM [DB].[sbo].[SALES]
  WHERE DUE_DATE BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
  GROUP BY Branch, DUE_DATE
       

) src
PIVOT
(
  SUM(Sales) 
  FOR Diena IN (' + @cols + ')
            ) p '

EXECUTE(@query)
Daniel
  • 35
  • 5
  • SQL Server 2017 and later have `STRING_AGG` for this. Right now SQL Server 2017 is the oldest version in mainstream support – Panagiotis Kanavos Dec 21 '21 at 16:25
  • 1
    `... FROM [DB].[sbo].[SALES] WHERE DUE_DATE BETWEEN @DATANUO AND EOMONTH(@DATANUO)..` and instead of execute(@query) —> `exec sys.sp_executesql @stmt=@query, @params=N'@DATANUO datetime', @DATANUO=@DATANUO;` – lptr Dec 21 '21 at 16:48
  • Yes, i have already added this EXECUTE sp_executesql [at]query, N'[at]DATANUO CHAR(10)', [at]DATANUO = [at]DATANUO and everything works! Thx a lot! – Daniel Dec 21 '21 at 16:55
  • 1
    Does this answer your question? [T-SQL: How to use parameters in dynamic SQL?](https://stackoverflow.com/questions/1036745/t-sql-how-to-use-parameters-in-dynamic-sql) – Charlieface Dec 21 '21 at 16:56

0 Answers0