I am attempting to pivot some data but need to have the columns across populate based on date variables so that each day in the sequence is a column. While the regular PIVOT logic seems to work fine, when I add the dynamic feature to spread the dates depending on the date window given I receive "is not a valid identifier." as part of my error message in the dynamic SQL. I think it may just be a matter of syntax but I have played with it everyway I can think of and can't seem to get this dynamic pivot query to run. Can you help?
If so, it would be greatly appreciated!
Thank you! Here is my code and error message...
declare @FirstDOM datetime, @LastDOM datetime
--set @FirstDOM = (select dateadd(d,-1,dateadd(mm,datediff(m,0,getdate()),1 )))
--set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0)))
--for testing the pivot
SET @FirstDOM = '2/1/2022' SET @LastDOM = '2/28/2022'
DECLARE @StartDate datetime SET @StartDate = (SELECT DATEADD(mm,-1,@FirstDOM))
DECLARE @EndDate datetime SET @EndDate = (SELECT DATEADD(mm,-1,@LastDOM))
DECLARE @DatesToUse varchar(500)
DECLARE @Dates table ([Date] datetime)
INSERT @Dates (Date) SELECT DISTINCT [Date] FROM [dbo].[PJMInvoiceActuals]
--SELECT * FROM @Dates WHERE [Date] > @StartDate AND [Date] < @EndDate
DECLARE @FormatedDates table ([Date] varchar(30))
INSERT @FormatedDates
SELECT DISTINCT CONVERT (DATE,[Date])AS Date FROM @Dates WHERE [Date] BETWEEN @StartDate AND @EndDate
SELECT * FROM @FormatedDates
DECLARE @colname varchar(500)
SET @colname = NULL
SELECT @colname = COALESCE(@colname + ',','') + QUOTENAME([Date])
FROM @FormatedDates;
SELECT @colname
--SELECT @colname
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery =
'with pivot_tbl as
(
SELECT [BillingLineItemType], '+@colname+'
FROM
(
SELECT CONVERT(DATE,[Date]) AS Date,[BillingLineItemType], Amount
FROM [dbo].[PJMInvoiceActuals]
WHERE CONVERT(DATE,[Date] ) BETWEEN ''2022-01-01'' AND ''2022-01-31''
) AS SourceTable
PIVOT
( Sum(Amount)
FOR [Date] IN ('+@colname+')
) AS PivotTable
)
--SELECT * FROM pivot_tbl'
EXEC @SQLQuery
--error msg
Msg 203, Level 16, State 2, Line 46
The name 'with pivot_tbl as
(
SELECT [BillingLineItemType], [2022-01-01],[2022-01-02],[2022-01-03],[2022-01-04],[2022-01-05],[2022-01-06],[2022-01-07],[2022-01-08],[2022-01-09],[2022-01-10],[2022-01-11],[2022-01-12],[2022-01-13],[2022-01-14],[2022-01-15],[2022-01-16],[2022-01-17],[2022-01-18],[2022-01-19],[2022-01-20],[2022-01-21],[2022-01-22],[2022-01-23],[2022-01-24],[2022-01-25],[2022-01-26],[2022-01-27],[2022-01-28]
FROM
(
SELECT CONVERT(DATE,[Date]) AS Date,[BillingLineItemType], Amount
FROM [dbo].[PJMInvoiceActuals]
WHERE CONVERT(DATE,[Date] ) BETWEEN '2022-01-01' AND '2022-01-31'
) AS Sourc' is not a valid identifier.