Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'DATEPART'.
ALTER Procedure [dbo].[Pivot_Attendance2] --@StartDate Date, --@Enddate Date As Begin SELECT DISTINCT ReportingDate INTO #Dates FROM EmployeesAttendance --WHERE (ReportingDate BETWEEN @StartDate AND @Enddate) ORDER BY ReportingDate DECLARE @cols NVARCHAR(4000) SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112) + ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']') FROM #Dates ORDER BY ReportingDate DECLARE @qry NVARCHAR(4000) = N'SELECT * FROM (SELECT EmpID, ReportingDate, CASE WHEN [Days] IS null AND EXISTS (SELECT 1 FROM EmpApplication WHERE EmployeesAttendance.Empid = EmpApplication.Empid AND (ReportingDate >= LeavFrom AND ReportingDate <= LeavTo)) THEN ' CL ' WHEN [Days] IS null AND EXISTS (SELECT 1 FROM Holidays WHERE ReportingDate = HolidayDate) THEN 'H' WHEN [Days] IS null THEN 'A' WHEN [Days] = 1 THEN 'P' END AS Status DATEPART(DAY, ReportingDate)as DDate FROM EmployeesAttendance PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat ' -- Executing the query EXEC(@qry) END
Status
... WHEN [Days] IS NULL THEN 'A' WHEN [Days] = 1 THEN 'P' END AS Status, -- comma was missing here DATEPART(DAY, ReportingDate) AS DDate FROM EmployeesAttendance ...
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)