SQL Server Performance

Keeping Track of members

Discussion in 'General Developer Questions' started by danny123, Oct 5, 2008.

  1. danny123 New Member

    Hi there,
    We have a table which keeps track of activities of all the members who attend the different events.Activity Type ID 5 means joined the event and Activity Type ID 6 meand left the event.
    Now i am working on a query which will give me result with 6 columns each as count of people in each hour in last 6 hours.For example if i run the query at 8 PM then there are 6 columns with count of members in each hour.
    [At 7 PM] [At 6 PM] [At 5 PM] [At 4 PM] [At 3 PM] [At 2 PM]
    4 3 11 2 0 5
    The issue which i am facing is if one member joins the event at 3 PM (So there will be a record with Activitt 5) and leave the event at 6 PM (Activity 6), i need to keep track of that member in each hour between them till it leaves the event and query sees the record for activity type 6 for that member and that event.So that member will be counted in each hour between joining and leaving time.
    Can someone suggest how to keep track of these members?
    Thanks!
  2. Adriaan New Member

    So you have a table of "Member Activity". What columns does it have? Plus some sample data would come in handy.
  3. danny123 New Member

    This is how the table looks like and i have included the test data also.
    ID MemberID Event ActivityTypeID DateTime
    1 11 34 5 10/03/2008 7:15AM
    2 12 34 5 10/03/2008 7:49AM
    3 13 34 5 10/03/2008 8:43AM
    4 14 34 5 10/03/2008 9:05AM
    5 14 34 6 10/03/2008 10:53AM
    6 15 34 5 10/03/2008 11:17AM
    7 11 34 6 10/03/2008 11:38AM
    8 16 34 5 10/03/2008 12:02PM
    9 17 34 5 10/03/2008 12:43PM
    10 18 34 5 10/03/2008 1:14PM
    11 12 34 6 10/03/2008 1:15PM
    12 13 34 6 10/03/2008 1:24PM
    13 15 34 6 10/03/2008 1:38PM
    14 16 34 6 10/03/2008 1:56PM
    15 17 34 6 10/03/2008 2:23PM
    The result should be like below. The Header is the Fields to count between those hours and i have added member Id in brackets which are counted in that hour. But all i need is count thats just to explain it here and Field name is also not a problem.
    [2-3] [1-2] [12-1] [11-12] [10-11] [9-10] [8-9] [7-8]
    2 6 5 4 4 4(11,12,13,14) 3(11,12,13) 2 (11,12)
    | | | | |
    | | | | (11,12,13,14) *14 is included because 14 was in the Event in this hour
    | | | |
    | | | (11,12,13,15) *11 is included bacause it was in event in this hour.
    | | |
    | | (12,13,15,16,17)
    | |
    | (12,13,15,16,17,18)
    |
    (17,18)
    Thanks for the help !!
  4. danny123 New Member

    Did i miss some information here ?
  5. Adriaan New Member

    So you want to group on the hour, and show the number of members who have logged into any event before or during this hour (ActivityTypeID=5 ?) and may or may not have logged out (ActivityTypeID=6 ?) of this same event.
    Create a SELECT query that returns the aggregate for each login hour:
    SELECT COUNT(*), t.Event, DATEADD(hh, DATEDIFF(hh, 0, getdate()), 0) AS DateTime_Hour
    FROM mytable t
    WHERE t.ActivityTypeID = 5
    GROUP BY t.Event, DATEADD(hh, DATEDIFF(hh, 0, t.DateTime), 0)
    To include the unended sessions from previous hours, add a NOT EXISTS subquery to the WHERE clause:
    WHERE t.ActivityTypeID = 5 AND NOT EXISTS
    (SELECT x.* FROM mytable x
    WHERE x.Event = t.Event AND x.MemberID = t.MemberID AND x.ActivityTypeID = 6
    AND DATEADD(hh, DATEDIFF(hh, 0, x.DateTime), 0) < DATEADD(hh, DATEDIFF(hh, 0, t.DateTime), 0))
    You'll have to work on that a little more to add up the counts of unended session attendance from previous hours, so I guess a temp table is in order. This will also make it easier to do the cross-tab.
    Another approach would be to fill a temp table with the hours, so you can update the temp table more easily.
    In SQL 2005, there is plenty of new syntax that will cover this type of requirement much better.

Share This Page