0

I am trying to complete an SQL statement where I build a query using a With Clause and then Pivot it around work week numbers. The work weeks are based around the next 10 work weeks and previous 10. The WITH Clause works perfectly building the initial query. However, I cannot select the dynamic columns from the CTE2 alias.

Any ideas?

declare @thisDate Date = '20151110';
DECLARE @DateFrom DATE = DateAdd(week,-10,@thisDate) ;
DECLARE @DateTo DATE = DateAdd(week,10,@thisDate) ;


WITH T(date) AS
     (SELECT @DateFrom 
      UNION all
     SELECT DateAdd(week,1,T.date) FROM T WHERE T.date < @DateTo
     )
,
CTE (proID,ms,ww) as
(select proID,max(mtypid) as ms, DatePart(week,MeetDate) as ww from tblMinMeeting where proID in(723,837) and MTYPID in(1,2,3,4) group by proid, DatePart(week,MeetDate)
union
select proID,9,DatePart(week,DesignStart) from tblProjects
union
select proID,7,DatePart(week,DesignFinish) from tblProjects 
),
CTE2 (proID,ww,ms)
as
( 
select CTE.proID, ww,mt.Name from T join 
CTE on datepart(week,T.date) = CTE.ww left outer join
(select MTYPID,Name from tblMinMeetType) mt on CTE.ms = mt.MTYPID
)

SELECT *
FROM (
select proID,ww,ms
FROM CTE2) as s
PIVOT
(MAX(MS) for ww IN(select STUFF((SELECT DISTINCT ',' +  QUOTENAME(CONVERT(VARCHAR,ww),'')
FROM CTE2
FOR XML PATH('')),1,1,''))) as PVTTable

The query doesn't recognize the CTE2 alias when using SELECT STUFF

here is the error

Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near ')'.
user1781272
  • 862
  • 2
  • 14
  • 25
  • 1
    1) You need dynamic PIVOT 2) Generating dates as recursive cte can be changed with tally table – Lukasz Szozda Nov 11 '15 at 12:17
  • But I am using a dynamic PIVOT. see Code`IN(select STUFF((SELECT DISTINCT ',' + QUOTENAME(CONVERT(VARCHAR,ww),'') FROM CTE2 FOR XML PATH('')),1,1,''))`. Also, I did use a tally table. Any more help would be appreciated. – user1781272 Nov 11 '15 at 12:37

0 Answers0