group by clause in right outer join

Question

I have one att table , it has two columns EmployeeId and Date.

 

EmployeeId | Date  

1. 10214 | 2017-01-02 08:16:00.000
2. 10215 | 2017-01-02 08:17:00.000
3. 10170 | 2017-01-02 08:45:00.000
4. 90    | 2017-01-02 09:20:00.000
5. 202   | 2017-01-02 09:38:00.000
6. 125   | 2017-01-02 09:40:00.000
7. 163   | 2017-01-02 09:42:00.000
8. 16    | 2017-01-02 09:53:00.000
9. 17    | 2017-01-02 09:53:00.000
10. 3     | 2017-01-02 09:55:00.000
11. 3     | 2017-01-03 09:55:00.000
12. 10214 | 2017-01-03 10:55:00.000
13. 10215 | 2017-01-03 10:55:00.000
14. 10215 | 2017-01-04 10:55:00.000

 

alter PROCEDURE GET_ATT

@date DATETIME AS BEGIN

with DaysInMonth as ( select @date as Date union all select dateadd(dd,1,Date) from DaysInMonth where month(date) = month(@Date)) select * into #TMP_DATES from DaysInMonth where month(date) = month(@Date)

DECLARE @COLUMN VARCHAR(MAX)

SELECT @COLUMN=ISNULL(@COLUMN+',','')+ '['+ CAST(CONVERT(DATE , T.Date) AS VARCHAR) + ']' FROM #TMP_DATES T

DECLARE @Columns2 VARCHAR(MAX)

SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+ CAST(CONVERT(DATE , Date) as varchar )+'],''N/A'') AS ['+CAST(CONVERT(DATE , Date) as varchar )+']' FROM #TMP_DATES GROUP BY Date FOR XML PATH('')),2,8000)

DECLARE @QUERY VARCHAR(MAX)

SET @QUERY ='SELECT EmployeeId, ' + @Columns2 +' FROM (SELECT A.EmployeeId,MIN(CAST(A.Date as time))as intime,B.Date AS DATE FROM Att A RIGHT OUTER JOIN #TMP_DATES B ON A.Date=B.Date GROUP BY A.EmployeeId) X
PIVOT
(
MIN([intime]) FOR [DATE] IN (' + @COLUMN + ')
) P
WHERE ISNULL(EmployeeId,'''')<>''''
'

EXEC (@QUERY)
DROP TABLE #TMP_DATES
END

 

 

I have tried this sp for display monthwise intime and outime of employee but it is getting error as

Column '#TMP_DATES.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

EXEC dbo.GET_ATT @date ='2017-01-02'