Syntax Error in query using PIVOT

Transact-SQL
1

MY Query is:

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.

Arthur

2

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];

If you want more help read:

3

Thank you however now I am receiving this error:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'X'.

It is the X near (DayDiff)

Arthur

4

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];

5

An extra closing braket is needed before the X. You did not provide test data. Glad you got something to work.