5
Reply

group by clause in right outer join

reshma tambe

reshma tambe

Feb 11 2017 4:15 AM
358
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'

Answers (5)