SELECT Count(dbo.T_Inspections.Job_Id) AS CountOfJob_Id , dbo.T_Inspections.Rep
FROM dbo.T_Inspections
GROUP BY dbo.T_Inspections.Rep,dbo.T_Inspections.Status
PIVOT(
(CASE
When DateDiff(day,[submt],[rep due])>=0,'Ext_OnTime'
When DateDiff(day,[submt],[rep due])=-1,'1_Day_Late'
When DateDiff(day,[submt],[rep due])=-2,'2_Day_Late'
When DateDiff(day,[submt],[rep due])<=-3,'3_Day_More_Late')
END);
) As PivotTable
I am getting an error as follows:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'PIVOT'.
I am sure others have asked this question but I am stumped. Please Help.
PIVOT is used before GROUP BY. Try a simple cross tab:
SELECT I.Rep, I.[Status]
,COUNT(I.Job_Id) AS CountOfJob_Id
,SUM(CASE WHEN X.DayDiff >= 0 THEN 1 ELSE 0 END) AS [Ext_OnTime]
,SUM(CASE WHEN X.DayDiff = -1 THEN 1 ELSE 0 END) AS [1_Day_Late]
,SUM(CASE WHEN X.DayDiff = -2 THEN 1 ELSE 0 END) AS [2_Day_Late]
,SUM(CASE WHEN X.DayDiff <= -3 THEN 1 ELSE 0 END) AS [3_Day_More_Late]
FROM dbo.T_Inspections I
CROSS APPLY ( VALUES (DATEDIFF(day, I.[submt], I.[rep due]) ) X (DayDiff)
GROUP BY I.Rep, I.[Status];
This s correct!
SELECT I.Rep, I.[Status], COUNT(I.Job_Id) AS CountOfJob_Id,
SUM(CASE WHEN DATEDIFF(day, I.[submt], I.[rep due]) >= 0 THEN 1 ELSE 0 END) AS [Ext_OnTime],
SUM(CASE WHEN DATEDIFF(day, I.[submt], I.[rep due]) = -1 THEN 1 ELSE 0 END) AS [1_Day_Late],
SUM(CASE WHEN DATEDIFF(day, I.[submt], I.[rep due]) = -2 THEN 1 ELSE 0 END) AS [2_Day_Late],
SUM(CASE WHEN DATEDIFF(day, I.[submt], I.[rep due]) <= -3 THEN 1 ELSE 0 END) AS [3_Day_More_Late]
FROM dbo.T_Inspections I
GROUP BY I.Rep, I.[Status];