Post reply

Query for concurrent users for a particular period.

  • May 28, 2010 at 10:49 am

    #221621

    Hi Everyone,

    I have a table that contains the login and logout time and here are the following columns and data:

    ID, Action, Time, UserName

    1, Login, 2010-05-01 13:00:00, abc123

    2. Logout, 2010-05-01 14:00:00, abc123

    3. Login, 2010-05-01 13:30:00, def234

    4. Logout, 2010-05-01 14:10:00, def234

    .

    .

    .

    .

    .

    .

    Can anyone help me write a query that will show max concurrent users let say for the day of May 1, 2010?

    Much appreciated with all your help! 🙂

  • May 28, 2010 at 4:20 pm

    #1174179

    This is slow and not elegant, but a cursor will work with your table structure:

    DECLARE @Action varchar(10),

    @Time datetime,

    @UserCount int,

    @MaxUsers int

    SELECT @UserCount = 0, @MaxUsers = 0

    DECLARE Cur CURSOR FOR SELECT [Action], [Time] FROM LogTable

    OPEN Cur

    FETCH NEXT FROM Cur INTO @Action, @Time

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @Action='Login'

    BEGIN

    SET @UserCount=@UserCount + 1

    IF @UserCount>@MaxUsers SET @MaxUsers=@UserCount

    END

    ELSE -- check > 0 in case logout is the first event

    IF @UserCount > 0 SET @UserCount=@UserCount - 1

    FETCH NEXT FROM Cur INTO @Action, @Time

    END

    CLOSE Cur

    DEALLOCATE Cur

    SELECT @MaxUsers

  • May 28, 2010 at 5:05 pm

    #1174184

    Thanks a lot hester84!! It works!!

    However, if I would like it to show the number of max concurrent users per day for a month of April, how can I do that?

  • May 28, 2010 at 8:02 pm

    #1174197

    David_W1234 (5/28/2010)

    Thanks a lot hester84!! It works!!

    However, if I would like it to show the number of max concurrent users per day for a month of April, how can I do that?

    By NOT using a cursor...

    -- See how you start off by actually creating a table

    -- and then inserting the data into it? Doing this really

    -- makes things a lot easier for all the people you want to

    -- help you. So, HELP US HELP YOU by doing this for us! See

    -- http://www.sqlservercentral.com/articles/Best+Practices/61537/

    -- for more details on how to do all of this.

    declare @test-2 table (ID int, [Action] varchar(6), [Time] datetime, UserName varchar(6))

    INSERT INTO @test-2

    SELECT 1, 'Login', '2010-05-01 13:00:00', 'abc123' UNION ALL

    SELECT 2, 'Logout', '2010-05-01 14:00:00', 'abc123' UNION ALL

    SELECT 3, 'Login', '2010-05-01 13:30:00', 'def234' UNION ALL

    SELECT 4, 'Logout', '2010-05-01 14:10:00', 'def234' UNION ALL

    SELECT 5, 'Login', '2010-05-02 13:00:00', 'xxxxxx' UNION ALL

    SELECT 6, 'Login', '2010-05-03 13:10:00', 'xxxzzz'

    -- whew!!! now that the test data has been made,

    -- let's see how to do what you're asking for!

    DECLARE @Date DATE

    -- set to the first day of the month

    SET @Date = '20100501'

    SELECT [Date] = DateAdd(day, 0, DateDiff(day, 0, [Time])),

    MaxUsers = SUM(CASE WHEN [Action] = 'Login' THEN 1

    WHEN [Action] = 'Logout' THEN -1

    ELSE 0 END)

    FROM @test-2

    WHERE [Time] >= @Date

    AND [Time] < DateAdd(month,1,@Date)

    GROUP BY DateAdd(day, 0, DateDiff(day, 0, [Time]))

    ORDER BY [Date]

    Edit: Please read the first link in my signature for how to get better, faster answers to your questions.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 29, 2010 at 9:43 am

    #1174256

    Itzik Ben-Gan ran a great series of articles on the maximum concurrent sessions problem a few months back.

    Anyone with a subscription can view the final article at http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3.aspx

    The fastest T-SQL solution was independently submitted by two people - one of which was our very own Barry Young.

    It is an awesome solution, and by far out-performs all other T-SQL solutions.

    It is possible to write an even faster version (20-30%) using SQLCLR.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 30, 2010 at 8:55 pm

    #1174376

    Wayne, your solution was MUCH better than mine. Cursors are evil 🙂

  • June 1, 2010 at 12:11 pm

    #1174954

    Thank you so much Wayne! Your solution works perfectly!! I will keep your advise in mind about asking for help next time.

    You guys are the best!:-D

  • June 1, 2010 at 1:32 pm

    #1174992

    hester84 (5/30/2010)

    Wayne, your solution was MUCH better than mine. Cursors are evil 🙂

    :blush: Thanks.

    Yes, they are. Unless you're in a situation where you actually want things to run slowly, but these are almost always tied to a maintenance operation. So, as Jeff Moden says, think about what you want to do to a column, not what you want to do to the row.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 8 posts - 1 through 7 (of 7 total)

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