get a count between a time range

23 Jun 2006 9:12 PM

TDT

I am trying to figure out if I can do this in a SQL query. I need to
know how many users are active during a given time period. I want to
be able to return the results below.   If you look at the row with
User3 I only want to count User2 once even though he was active twice
during the time frame for User3

Start Time            End Time           User Id   Concurrent User Count
06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1     1
06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1     3
06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2     3
06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3     3
06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2     2
06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2     1

I am pretty much stumped as how to proceed.

Thanks Tim

23 Jun 2006 9:28 PM

Lubdha Khandelwal

Assuming you're trying to query users who were active for any part of
the duration @StartTime...@EndTime inclusive.

SELECT DISTINCT UserId
FROM Table1
WHERE ( (StartTime <= @StartTime AND EndTime >= @StartTime)
OR (StartTime BETWEEN @StartTime AND @EndTime) )

23 Jun 2006 9:38 PM

TDT

If you look at User3 there were 3 users active during User3's the start
time and end time including User3. I need to figure that count.

Thanks Tim

Lubdha Khandelwal wrote:
Show quote

> Assuming you're trying to query users who were active for any part of
> the duration @StartTime...@EndTime inclusive.
>
> SELECT DISTINCT UserId
> FROM Table1
> WHERE ( (StartTime <= @StartTime AND EndTime >= @StartTime)
> OR (StartTime BETWEEN @StartTime AND @EndTime) )

23 Jun 2006 9:50 PM

Lubdha Khandelwal

I'm confused. Do you want to count all the unique users that were
active during each of the active duration for all the users?

If so, this could get you that count:

SELECT T1.UserId, COUNT(DISTINCT T2.UserId)
FROM UserTable T1 INNER JOIN UserTable T2
ON ( (T2.StartTime <= T1.StartTime AND T2.EndTime >= T1.StartTime)
OR (T2.StartTime BETWEEN T1.StartTime AND T1.EndTime) )
GROUP BY T1.UserId

23 Jun 2006 10:18 PM

Erland Sommarskog

TDT (tim.truji***@gmd.com) writes:
Show quote

> I am trying to figure out if I can do this in a SQL query. I need to
> know how many users are active during a given time period. I want to
> be able to return the results below.   If you look at the row with
> User3 I only want to count User2 once even though he was active twice
> during the time frame for User3
>
> Start Time              End Time            User Id   Concurrent User
> Count
> 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1     1
> 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1     3
> 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2     3
> 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3     3
> 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2     2
> 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2     1
>
> I am pretty much stumped as how to proceed.

I assume that the above is the desired output. But how does the input
look like? It would help to have the CREATE TABLE statement and
INSERT statements for the test data. Then it would be simple to copy-paste
into a query tool to develop a tested query.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

23 Jun 2006 10:46 PM

TDT

Lubdha that last query did not work. I was geting resource limit has
been reached and the query was cancelled. But here is script to create
the table and insert rows.

CREATE TABLE [dbo].[UserActivity]
(
    [StartTime] [datetime] NULL,
    [EndTime] [datetime] NULL,
    [UserID] [varchar](50) NULL
)

INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')

Erland Sommarskog wrote:
Show quote

> TDT (tim.truji***@gmd.com) writes:
> > I am trying to figure out if I can do this in a SQL query. I need to
> > know how many users are active during a given time period. I want to
> > be able to return the results below.   If you look at the row with
> > User3 I only want to count User2 once even though he was active twice
> > during the time frame for User3
> >
> > Start Time              End Time            User Id   Concurrent User
> > Count
> > 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1     1
> > 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1     3
> > 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2     3
> > 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3     3
> > 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2     2
> > 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2     1
> >
> > I am pretty much stumped as how to proceed.
>
> I assume that the above is the desired output. But how does the input
> look like? It would help to have the CREATE TABLE statement and
> INSERT statements for the test data. Then it would be simple to copy-paste
> into a query tool to develop a tested query.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

24 Jun 2006 12:04 AM

Roy Harvey

Thanks for providing the table and data.

SELECT A.StartTime,
       A.EndTime,
       A.UserID,
       count(distinct B.UserID) as ConcurrentUserCount
FROM UserActivity as A
JOIN UserActivity as B
    ON A.StartTime between B.StartTime and B.EndTime
    OR A.EndTime between B.StartTime and B.EndTime
    or B.StartTime between A.StartTime and A.EndTime
GROUP BY A.StartTime,
       A.EndTime,
       A.UserID
ORDER BY 1, 2, 3

Roy Harvey
Beacon Falls, CT

Show quote

On 23 Jun 2006 15:46:23 -0700, "TDT" <tim.truji***@gmd.com> wrote:

>Lubdha that last query did not work. I was geting resource limit has
>been reached and the query was cancelled. But here is script to create
>the table and insert rows.
>
>CREATE TABLE [dbo].[UserActivity]
>(
>    [StartTime] [datetime] NULL,
>    [EndTime] [datetime] NULL,
>    [UserID] [varchar](50) NULL
>)
>
>
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:21 AM', '06/22/2006 6:38:25 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:38:56 AM','06/22/2006 6:39:05 AM','User1')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:03 AM','06/22/2006 6:39:07 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:04 AM','06/22/2006 6:39:08 AM','User3')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:07 AM','06/22/2006 6:39:11 AM','User2')
>INSERT INTO UserActivity (StartTime, EndTime, UserID) Values
>('06/22/2006 6:39:22 AM','06/22/2006 6:39:24 AM','User2')
>
>
>
>Erland Sommarskog wrote:
>> TDT (tim.truji***@gmd.com) writes:
>> > I am trying to figure out if I can do this in a SQL query. I need to
>> > know how many users are active during a given time period. I want to
>> > be able to return the results below.   If you look at the row with
>> > User3 I only want to count User2 once even though he was active twice
>> > during the time frame for User3
>> >
>> > Start Time              End Time            User Id   Concurrent User
>> > Count
>> > 06/22/2006 6:38:21 AM 06/22/2006 6:38:25 AM User1     1
>> > 06/22/2006 6:38:56 AM 06/22/2006 6:39:05 AM User1     3
>> > 06/22/2006 6:39:03 AM 06/22/2006 6:39:07 AM User2     3
>> > 06/22/2006 6:39:04 AM 06/22/2006 6:39:08 AM User3     3
>> > 06/22/2006 6:39:07 AM 06/22/2006 6:39:11 AM User2     2
>> > 06/22/2006 6:39:22 AM 06/22/2006 6:39:24 AM User2     1
>> >
>> > I am pretty much stumped as how to proceed.
>>
>> I assume that the above is the desired output. But how does the input
>> look like? It would help to have the CREATE TABLE statement and
>> INSERT statements for the test data. Then it would be simple to copy-paste
>> into a query tool to develop a tested query.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

25 Jun 2006 4:01 AM

--CELKO--

This is not a table; it has no key and no possible way to get a key.
If you knew what a table was, would you have written this? If you knew
either ISO-8601 or SQL, would you have used the proper temporal
formats?

CREATE TABLE UserActivity
(user_id VARCHAR(50) NOT NULL, -- magic data type used by newbies!
start_time DATETIME NOT NULL,
end_time DATETIME, -- null = current
PRIMARY KEY (user_id, start_time))

>> I need to know how many users are active during a given time period. <<

Where is the DDL for your reporting periods? What is your
grandularity? There are some specs missing here. However, you can get
samples at points in time; Set up a VIEW or TABLE with times in it.

SELECT R.report_time, COUNT(user_id) AS active_user_cnt
FROM ReportPeriods AS R, UserActivity AS U
WHERE R.report_time BETWEEN U.start_time AND U.end_time
GROUP BY R.report_time;

This actually works pretty well with a fine grandularity for simple
time series analysis. But if you wanted to do (n)-minute durations,
then we need a very complex set of rules for logging in and out within
a single duration.