getting # of monthly users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

getting # of monthly users

Author

Topic

a_nndy
Starting Member

4 Posts

Posted - 2008-06-16 : 14:09:48

I have a log_time_stamp coloum which has time stamp for all the users using the system....If user has logged in/logged out then time stamp is maintained for that user in that table.

This is part of table structure:
coloum: log_time_stamp
coloum: username
coloum: action

I now want to get total number of users in a month that have used the system.

so if start date is 02/2008
end date is 05/2008

then output should be(two coloums)month and # of users accessd system in that month.
02---number of users
03---number of users
04---number of users
05---number of users

Can any one tell how we can approach this

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-06-16 : 14:23:26

select month(log_time_stamp), count(*)
from YourTable
Group By month(log_time_stamp)
Having month(log_time_stamp) Between 2 and 5
and Year(log_time_stamp) = 2008

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

a_nndy
Starting Member

4 Posts

Posted - 2008-06-16 : 14:30:29

Column 'auditlog.log_stamp' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

I am getting this error.

Also can u show using variables start date and end date.

Sorry but I am a newbeee to T-sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 14:31:55

[code]select year(log_time_stamp),month(log_time_stamp), count(username)
from YourTable
where log_time_stamp >=@StartDate
AND log_time_stamp <=@EndDate
Group By year(log_time_stamp),month(log_time_stamp)[/code]
just in case your startdate & enddate happen to be in two different years.

a_nndy
Starting Member

4 Posts

Posted - 2008-06-17 : 04:00:03

Hello Guys,

thanks for all your help. The query seems to be running fine. The only things is that I think it is not taking care for multiple user entries.
Like when user logs in, it is noted(log_time_stamp) in the database also when user logs out it is noted in the database. So we have multiple time stamp for one user. So I am not sure if the above queries take for that. Can any 1 tell how we can take care of this.
So in the total # of monthly users, there should be unique users in that month.

Thanks,

Aniketh

ramireddy
Starting Member

4 Posts

Posted - 2008-06-17 : 04:52:37

select year(log_time_stamp),datename(month,log_time_stamp),count(distinct Username) from userlogs
group by year(log_time_stamp),datename(month,log_time_stamp)

a_nndy
Starting Member

4 Posts

Posted - 2008-06-17 : 12:12:33

this is the wuery I am finally using:

Select distinct(sum(1)) as 'Number of Users' , Cast(month(log_time_stamp) as varchar(2)) + '/' + Cast(year(log_time_stamp) as char(4)) as Months
FROM mytable'
GROUP BY Cast(month(log_stamp) as varchar(2)) + '/' + Cast(year(log_stamp) as char(4))
order by 2

In the 1st returning coloum sum(1) is adding the multiple user of the same name. I just want only 1 user to be included once in a month.

I hope I have not confused.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources