Предложение Group by в правом внешнем соединении
I have one att table , it has 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
Что я уже пробовал:
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.
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'