Dynamic pivot returns PIVOT operator error

[Chuck712]

I am trying to construct a dynamic pivot query for a report that uses a company period counts table for monthly membership by chapter. What the table has a column for the year and a separate column for the month as there is a stored procedure that fires monthly to populate the values for the previous month. What I was looking for was a way to have the query dynamically put the results into a report, adding the next months column when it was present. The months are simple numbers 1,2,3,4,5 ect. I am getting this error "Msg 207, Level 16, State 1, Line 4
Invalid column name '1'." for each month number and then "Msg 265, Level 16, State 1, Line 4
The column name "1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument."

The query is

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @col AS VARCHAR(MAX)
DECLARE @start_date datetime --first day of last month
DECLARE @finish_date datetime --last day of last month
DECLARE @this_month datetime --current day of last month
SET @this_month = DATEADD(mm,-1,GetDate())
SET @start_date = CONVERT(datetime,STR(MONTH(@this_month))+'/01/'+
STR(YEAR(@this_month)),101)
IF MONTH(@this_month)<12
SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@this_month)+1,2)
+'/01/'+STR(YEAR(@this_month))+ ' 23:59:00',101))
ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,'01/01/'+STR(YEAR(@this_month)+1)+ ' 23:59:00',101))

select @col = STUFF((SELECT ',' + QUOTENAME(h10_month)
from (select distinct h10_month from client_company_period_counts
where h10_year = datepart(yyyy,GETDATE())) as h10_month

group by h10_month
order by h10_month
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @sql = N'


SELECT * from(
SELECT h10_chp_code,
h10_chp_name,
h04_division_code,
chs_code,
h10_calculated_balance,
' +@col+ '
from client_company_period_counts pc
join co_chapter cp(nolock) on cp.chp_cst_key = pc.h10_chp_cst_key
join co_chapter_ext cx on cp.chp_cst_key=cx.chp_cst_key_ext
join client_company_division d on cx.chp_h04_key_ext = d.h04_key
join co_chapter_status cs (nolock)on cs.chs_key = cp.chp_chs_key

WHERE DATEPART(yyyy,' + convert(nvarchar,@finish_date,101) + ') = h10_year
AND chp_cht_key not in (''08A654F6-9A0A-41A2-B081-1A31BB1BE45A''
,''F3970CE2-9AF4-4761-81D3-05B9E5D43853'')
AND chp_asn_key = ''DEA92516-84C1-4E65-937B-F45349BD3159''
AND chp_terminate_date is null
AND h10_delete_flag = 0
)SRC
PIVOT
(Sum(h10_calculated_balance)
FOR h10_month in ('+@col+'))AS PVT';

execute sp_executesql @sql;

Any help is greatly appreciated.

 

[Chuck712]

I have found the issue. WHERE DATEPART(yyyy,' + convert(nvarchar,@finish_date,101) + ') = h10_year was throwing everything off..