# question

## Count for Attendance of employees

Here is the table: Date LoggedIn_emp 1/1/2012 emp1 1/1/2012 emp2 1/1/2012 emp3 1/2/2012 emp1 1/2/2012 emp2 1/2/2012 emp4 ...... ...... 1/31/2012 emp1 1/31/2012 emp2 I need to count during 1/1/2012 to 1/31/2012, how many times each employee has logged on. for example, output should be like: daterange emp count month of Jan emp1 28 month of Jan emp2 30 .... .... month of Feb emp1 28 month of Feb emp2 27 month of Feb emp3 27 .... ..... Thank you in advance :)

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

·
Give a try to SELECT 'month of Jan" as daterange, LoggedIn_emp as emp, sum(LoggedIn_emp) as [Count] from table where daterange >= '2012/01/01' and daterange
1 comment

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

Hi SirSQL, LoggedIn_emp is a varchar field, SUM wont work. Error: Operand data type varchar is invalid for sum operator. Could you suggest another solution ??
0 Likes 0 ·
Try this: SELECT DATENAME(MONTH,[date]),LoggedIn_emp, COUNT(LoggedIn_emp) FROM tableName GROUP BY DATENAME(MONTH,[date]), LoggedIn_emp I think that this will give you a start. Hope this helps! Thanks!

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

Or if you want to see the month along with the year for readability, you could do something like this: SELECT CAST(DATEPART(MONTH,[date]) AS VARCHAR(15))+'/'+CAST(DATEPART(YEAR,[Date]) AS VARCHAR(4)),LoggedIn_emp, COUNT(LoggedIn_emp) FROM tableName GROUP BY CAST(DATEPART(MONTH,[date]) AS VARCHAR(15))+'/'+CAST(DATEPART(YEAR,[Date]) AS VARCHAR(4)), LoggedIn_emp
0 Likes 0 ·
Heh. Looks like you were just finishing off your answer as I was starting mine. :-)
0 Likes 0 ·
Yup! Solutions passing in the wind! ;-)
0 Likes 0 ·
Just cleaned it up by removing the 'datepart' function. Much cleaner and I don't know what I was thinking. ;-) SELECT CAST(MONTH([date]) AS VARCHAR(15))+'/'+CAST(YEAR([Date]) AS VARCHAR(4)),LoggedIn_emp, COUNT(LoggedIn_emp) FROM tableName GROUP BY CAST(MONTH([date]) AS VARCHAR(15))+'/'+CAST(YEAR([Date]) AS VARCHAR(4)), LoggedIn_emp
0 Likes 0 ·
In general, you might want to try something along these lines: select year(LoginDate) AS DateYear, datename(month, LoginDate) AS DateMonth, LoggedIn_Emp AS emp, COUNT(*) FROM @LoginData -- WHERE clause goes here GROUP BY Year(LoginDate), datename(MONTH,LoginDate), LoggedIn_Emp You can use a `WHERE` clause to narrow down the data to a particular year easily enough. -caveat- However, I'm not sure that this will necessarily give the "right" data - what if someone has to log in several times in a day? What if someone leaves themselves logged in for several days at a time?

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