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 :)
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
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!
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
Heh. Looks like you were just finishing off your answer as I was starting mine. :-)
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
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?
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.
5 People are following this question.
