group by clause in right outer join

reshma tambe

9y1.1k

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

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'

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
that means you have solved Group by problem,
and here " Conversion failed when converting date and/or time from character string "
for this check the string field where you are converting to datetime is getting proper datetime or not. may be in the string date field doesn't have proper datetime format
Bikesh Srivastava9y ago
I think dosen't matter what type of join ou are using to groupby.
its mainly use to group info.
Nitin Sontakke9y ago
Hello Reshma,

I have spent few minutes of a Sunday morning writing a better query for you. Please see the attached.

There is one more improvement which can be done to this.

Those employees who were not present the entire month are not being reported. There is a way to report them with the help of cross join between employee master and the dates table.

Let me know you need help doing this. Also mention name of the employee master table.

I didn't particularly like that the reserved words (such as Date) are being used as column names. Please be bit more imaginative while giving column names.
reshma tambe9y ago
now its getting error as
" Conversion failed when converting date and/or time from character string "
Rafnas T P9y ago
you have to give all other column name after giving GROUP BY A.EmployeeId like below

GROUP BY A.EmployeeId,A.Date,B.Date