How many concurrent users???

  • Ash.Shah

    SSC Eights!

    Points: 982

    Hello all,

    I have a table as follows:

    CREATE TABLE t_log

    (

    sactioncode varchar(10),

    tscreated datetime,

    swinusername varchar(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

  • Jack Corbett

    SSC Guru

    Points: 184371

    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

  • Carl Federl

    One Orange Chip

    Points: 25384

    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

  • RBarryYoung

    SSC Guru

    Points: 143327

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply