question

resh avatar image
resh asked

group by clause in right outer join

I have one att table , it have two columns EmployeeId and Date. | EmployeeId | Date | |----------------|---------| 10214 | 2017-01-02 08:16:00.000 10215 | 2017-01-02 08:17:00.000 10170 | 2017-01-02 08:45:00.000 90 | 2017-01-02 09:20:00.000 202 | 2017-01-02 09:38:00.000 125 | 2017-01-02 09:40:00.000 163 | 2017-01-02 09:42:00.000 16 | 2017-01-02 09:53:00.000 17 | 2017-01-02 09:53:00.000 3 | 2017-01-02 09:55:00.000 3 | 2017-01-03 09:55:00.000 10214 | 2017-01-03 10:55:00.000 10215 | 2017-01-03 10:55:00.000 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 **EXEC dbo.GET_ATT @date ='2017-01-02'** I have tried this sp for display monthwise intime and outime of employee but it is getting error **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.**
sql-server-2008sqlsql-server-2008-r2sql-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Lukasz8519 avatar image
Lukasz8519 answered
hi, check this statement 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 You have 3 element in this query, 1 column, aggregate function and 3 column this query return error because You forgot to add 3 element to group by clause, when You add DATE to group by everything should be fine :) best regards
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.