CREATE PROC [dbo].[sp_STUD1_GetReportPrepAttendance]
(
	@year INT
)
AS
--DECLARE @year INT = 2017
DECLARE @dateCols VARCHAR(MAX),
		@dateColsMax VARCHAR(MAX)

SELECT	@dateCols = STUFF((SELECT ',' + QUOTENAME(P.Prep2017_SessionReviewTool_Date)
                FROM dbo.qt_PrepProgramme2017 P
				GROUP BY P.Prep2017_SessionReviewTool_Date
				ORDER BY P.Prep2017_SessionReviewTool_Date
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

SELECT	@dateColsMax = STUFF((SELECT ',MAX(' + QUOTENAME(P.Prep2017_SessionReviewTool_Date) +') AS '+ QUOTENAME(P.Prep2017_SessionReviewTool_Date)
                FROM dbo.qt_PrepProgramme2017 P
				GROUP BY P.Prep2017_SessionReviewTool_Date
				ORDER BY P.Prep2017_SessionReviewTool_Date
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

DECLARE @query VARCHAR(MAX)

SELECT	@query	= '
SELECT	Prep2017_SessionReviewTool_PrepProgrammeYear [Year],
        Prep2017_SessionReviewTool_Name [Name],
        Prep2017_SessionReviewTool_Surname [Surname],
        Prep2017_SessionReviewTool_School [School],
		'+@dateColsMax+'
		,CAST(dbo.fn_STUD_1_GetPrepAttendancePrecentage(Prep2017_SessionReviewTool_Name,Prep2017_SessionReviewTool_Surname,Prep2017_SessionReviewTool_PrepProgrammeYear) AS VARCHAR(5))+''%'' [Attendance %]
FROM
(
		SELECT	Prep2017_SessionReviewTool_PrepProgrammeYear ,
				Prep2017_SessionReviewTool_Name ,
				Prep2017_SessionReviewTool_Surname ,
				Prep2017_SessionReviewTool_School ,
				'+@dateCols+'

		FROM	dbo.qt_PrepProgramme2017
PIVOT
(
	MAX(Prep2017_SessionReviewTool_Attendance) 
	FOR Prep2017_SessionReviewTool_Date
	IN 
		(
			'+@dateCols+'
		)
) PivotTable 
) A
WHERE	Prep2017_SessionReviewTool_PrepProgrammeYear = '+CAST(@year AS VARCHAR(10))+'
GROUP BY Prep2017_SessionReviewTool_PrepProgrammeYear ,
        Prep2017_SessionReviewTool_Name ,
        Prep2017_SessionReviewTool_Surname ,
        Prep2017_SessionReviewTool_School 
ORDER BY  Prep2017_SessionReviewTool_Name ,
        Prep2017_SessionReviewTool_Surname
'
PRINT @query
EXEC(@query)


