question

sqlhungry avatar image
sqlhungry asked

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 :)
tsqlcount
10 |1200 characters needed characters left characters exceeded

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

SirSQL avatar image
SirSQL answered
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
10 |1200 characters needed characters left characters exceeded

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 ·
JohnM avatar image
JohnM answered
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!
4 comments
10 |1200 characters needed characters left characters exceeded

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 ·
ThomasRushton avatar image
ThomasRushton answered
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?
10 |1200 characters needed characters left characters exceeded

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

sqlhungry avatar image
sqlhungry answered
I will try that guys, thanks. Thomas, The example I gave was similar to what I have to track, I needed the logic to count the no. of emp's activity for the given range of time. My actual requirement if to track the count of frequency of employees who are working overtime at given time range. And there are certain events that can be used to track that emp is working overtime.
10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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