Click here to Skip to main content
15,886,026 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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


What I have tried:

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'
Posted
Updated 10-Feb-17 23:21pm

1 solution

 
Share this answer
 
Comments
resht 11-Feb-17 5:40am    
now it getting error as
Conversion failed when converting date and/or time from character string.
OriginalGriff 11-Feb-17 5:46am    
So check your data and see exactly what dates it is trying to convert - I don't have access to your database to identify what value is invalid!
resht 11-Feb-17 5:52am    
date column is in datetime format
OriginalGriff 11-Feb-17 6:40am    
"Conversion failed when converting date and/or time from character string."
Note the "from" in that - it's important.
So what is it trying to convert? What line?
resht 14-Feb-17 23:44pm    
thanks. error has been solved.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900