October 22, 2008 at 9:52 am
Hello all,
I have a table as follows:
CREATE TABLE t_log
(
sactioncodevarchar(10),
tscreateddatetime,
swinusernamevarchar(10)
)
The first column will show either LOGON or LOGOFF. The second column will show the datetime that a user performed the action of logging in or logging off. The third column gives the username.
So for example, if I logged on at 9.00 in the morning and logged off at 5.00 in the evening I would create two records in this table.
What I would like to know is the maximum number of concurrent users there has ever been i.e. the highest number of users logged on at the same time.
I personally cannot even begin to think how you would do this so any birght ideas to get me started would be much appreciated.
Thanks,
Ash
October 22, 2008 at 10:18 am
What is the time frame for which you would like to see concurrent users? Second, Minute, Hour?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2008 at 2:52 pm
Itzik Ben-Gan, a SQL Server MVP, has exactly the solution !
The problem involves calculating the maximum number of concurrent sessions for each application that an organization uses. For this problem, a table called Sessions stores information about application use. Each row contains one session's worth of data, including the application, user, host, start time, and end time
http://www.sqlmag.com/Article/ArticleID/37636/sql_server_37636.html
SQL = Scarcely Qualifies as a Language
October 22, 2008 at 11:30 pm
Like this:
drop table #t_log
drop table #t_periods
CREATE TABLE #t_log(
sactioncode varchar(10),
tscreated datetime,
swinusername varchar(10))
CREATE table #t_periods(UserName varchar(10), CheckIn datetime, CheckOut datetime)
Insert into #t_periods(UserName, CheckIn)
Select swinusername, tscreated
From #t_log
Where sactioncode = 'LOGON'
Update #t_periods
Set CheckOut = (Select MIN(tscreated)
From #t_log
Where UserName=swinusername
And sactioncode='LOGOFF')
Select MAX([TotalConcurrent])
From (Select P.*
, (select count(*) from #t_periods p2
Where p.CheckIn between p2.Checkin and p2.checkOut)
+ 1 As [TotalConcurrent]
From #t_periods P) P0
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply