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 :)
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.