USE DWDashboardSource ;
GO
CREATE PROCEDURE dbo.pStaffTimeDetailPivot_Center
(@Center CHAR(3)) AS
SET NOCOUNT ON
;
BEGIN TRY
	DECLARE
	@CNO CHAR(3)
	,@listCol VARCHAR(2000)
	,@Query NVARCHAR(4000)
	;
	SELECT @CNO = @Center
	;
	SELECT
	@listCol =
	STUFF
	(
		(
			SELECT DISTINCT '],[' + Date
			FROM dbo.vStaffTimeDetails_CenterDateRange
			ORDER BY '],[' + Date
			FOR XML PATH('')
		), 1, 2, ''
	) + ']'
	;
	--PRINT @listCol ;

	SELECT
	@query = '
	SELECT Center,Position,Employee,' + @listCol + '
	FROM
	(
		SELECT Center,Position,Employee,[Date],FloorHours
		FROM dbo.vStaffTimeDetails_CenterDateRange
		WHERE Position NOT IN (''Physician'',''Other'')
		AND Center = ' + @CNO + '
	) x
	PIVOT
	(
		SUM(FloorHours) FOR [Date] IN ('+@listCol+')
	) AS pvt
	'
	;
	EXEC sp_executesql @Query 
	;
END TRY
BEGIN CATCH
	SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO