sql server - SQL pivot query error: Unclosed quotation mark -

i have 3 tables (1. emp, 2. onleave,3.daysweeks). im trying write pivot query displyas empname, , calenderdates columns)

im getting following error, when try run pivot query.

1.unclosed quotation mark after character string '2016-12-31])) pvt'.

2.incorrect syntax near '2016-12-31])) pvt'

    declare @cols nvarchar(max), @query  nvarchar(max)  select @cols = stuff((select  ',' + quotename([caldate])                      daysweeks             xml path(''), type).value('.', 'nvarchar(max)')          ,1,1,'')  select @query =  'with cte ( select empdays.employee,empdays.caldate, isnull(v.vacationtype,1) leavestatus dbo.onleave v right outer join  (select e.empid employee, dw.caldate caldate, dw.dayofweekname downame   daysweeks dw,       dbo.emp e       dw.calyear = 2016 , dw.monthnumber=1) empdays on    v.empid = empdays.employee    , v.startingdate <= empdays.caldate     , v.endingdate >= empdays.caldate ) select * cte pivot  (max(cte.leavestatus) cte.caldate in([' + @cols + '])) pvt'   exec sp_executesql @query 

since quotename added here

 stuff((select  ',' + quotename([caldate])  

you don't need [ , ] here,

 cte.caldate in([' + @cols + '])) pvt' 

which wrong. make entire pivot column list consider single column

change

 cte.caldate in(' + @cols + ')) pvt' 

update: filter records passing deptid value (@deptid smallint =1). change query this

declare @deptid smallint = 1  ...... right outer join  (select e.empid employee, dw.caldate caldate, dw.dayofweekname downame   daysweeks dw,       dbo.emp e       dw.calyear = 2016          , dw.monthnumber=1          , e.deptid = @deptid ) empdays ......  exec sp_executesql @query,n'@deptid samllint',@deptid = @deptid