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!

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

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 !!

Did i miss some information here ?

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.