how to do a count per timeperiod

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to do a count per timeperiod

Author

Topic

Zathras
Starting Member

28 Posts

Posted - 2005-03-01 : 17:24:23

Image there is a table that includes a TimeIn and TimeOut fields (both datetime). I need to do an evaluation over time of how many users are in the table concurrently.

What would be the best approach to take for something like this? I would like to eventually feed this into a charting program that would draw a plot of # of concurrent users vs. time.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-03-02 : 00:42:15

You will probably need a table with predefined timeintervals eg 1 Hour intervals. Then join your table (with the time in and out) data onto this predefined time intervals table but only on the time period (excluding the date part obviously) using probably a between clause in your join.
This means that your predefined date table would look something like this:
CREATE TABLE TimePeriod(TimePeriodDisplay VARCHAR(20), StartTime VARCHAR(8), EndTime VARCHAR(8))

INSERT INTO TimePeriodDisplay
SELECT '00:00:00 - 00:59:59', '00:00:00', '00:59:59' UNION ALL
SELECT '01:00:00 - 01:59:59', '01:00:00', '01:59:59' UNION ALL
SELECT '02:00:00 - 02:59:59', '02:00:00', '02:59:59' UNION ALL
SELECT '03:00:00 - 03:59:59', '03:00:00', '03:59:59' UNION ALL
SELECT '04:00:00 - 04:59:59', '04:00:00', '04:59:59' UNION ALL
SELECT '05:00:00 - 05:59:59', '05:00:00', '05:59:59' UNION ALL
SELECT '06:00:00 - 06:59:59', '06:00:00', '06:59:59' UNION ALL
SELECT '07:00:00 - 07:59:59', '07:00:00', '07:59:59' UNION ALL
SELECT '08:00:00 - 08:59:59', '08:00:00', '08:59:59' UNION ALL
SELECT '09:00:00 - 09:59:59', '09:00:00', '09:59:59' UNION ALL
SELECT '10:00:00 - 10:59:59', '10:00:00', '10:59:59' UNION ALL
SELECT '11:00:00 - 11:59:59', '11:00:00', '11:59:59' UNION ALL
SELECT '12:00:00 - 12:59:59', '12:00:00', '12:59:59' UNION ALL
SELECT '13:00:00 - 13:59:59', '13:00:00', '13:59:59' UNION ALL
SELECT '14:00:00 - 14:59:59', '14:00:00', '14:59:59' UNION ALL
SELECT '15:00:00 - 15:59:59', '15:00:00', '15:59:59' UNION ALL
SELECT '16:00:00 - 16:59:59', '16:00:00', '16:59:59' UNION ALL
SELECT '17:00:00 - 17:59:59', '17:00:00', '17:59:59' UNION ALL
SELECT '18:00:00 - 18:59:59', '18:00:00', '18:59:59' UNION ALL
SELECT '19:00:00 - 19:59:59', '19:00:00', '19:59:59' UNION ALL
SELECT '20:00:00 - 20:59:59', '20:00:00', '20:59:59' UNION ALL
SELECT '21:00:00 - 21:59:59', '21:00:00', '21:59:59' UNION ALL
SELECT '22:00:00 - 22:59:59', '22:00:00', '22:59:59' UNION ALL
SELECT '23:00:00 - 23:59:59', '23:00:00', '23:59:59'

And then the join logic would look something like this
ON (CAST(DATEPART(hh, t.TimeIn) AS VARCHAR(8)) + ':' + CAST(DATEPART(mi, t.TimeIn) AS VARCHAR(8)) + ':' CAST(DATEPART(ss, t.TimeIn) AS VARCHAR(8)) BETWEEN i.StartTime AND i.EndTime OR
CAST(DATEPART(hh, t.TimeOut) AS VARCHAR(8)) + ':' + CAST(DATEPART(mi, t.TimeOut) AS VARCHAR(8)) + ':' CAST(DATEPART(ss, t.TimeOut) AS VARCHAR(8)) BETWEEN i.StartTime AND i.EndTime)

Wow - I hope that makes sense

Duane.

Zathras
Starting Member

28 Posts

Posted - 2005-03-02 : 15:29:19

Still looking for some more ideas if anyone else has some.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-03-02 : 17:41:02

Check this out:

http://www.sqlteam.com/item.asp?ItemID=1237

Specifically, look at the updated solution.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-02 : 17:42:09

Good problem...Does this imaginary table of yours with the timein and timeout columns get populated realtime? Or is it sumarized data from some other table(s)?

If the table gets populated real time are there records in there without a timeout value?

But anyway, first thought (for going foward) would be something like to have a job that runs every few seconds to get the current count of concurrent users and make inserts to a table with a timestamp and ConcurrentUserCount.
EDIT:
SElect count(*) from Usage where getDate() between timein AND isNull(timeout,getdate())

Then charting queries would be simple ie...

for concurrent usage by minute over last hour:

SElect	datepart(minute,getdate())
	,max(ConcurentUserCount) [count]
From	Usage
Where	countTime > dateAdd(hour,-1,getDate())
Group by datepart(minute,getdate())

for concurrent usage by hour over last day:


SElect	datepart(hour,getdate())
	,max(ConcurentUserCount) [count]
From	Usage
Where	countTime > Between '3/1/2005' AND '3/2/2005'
Group by datepart(hour,getdate())

But for any time over past history using your imaginary table, I'd like to give it some more thought.

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-02 : 17:54:08

Well you need a "intervals" table to plot against,
like the one Duane did.
Here is another example of how you can do this:

( it's somewhat cryptic, but you might be able to decrypt it by running it step by step in QA )

set nocount on

-- test data....
create table #period(timein datetime, timeout datetime, check(timein<timeout))
while (select count(*) from #period) < 100
	insert #period(timein,timeout)
	select din,dout from
	(select dateadd(minute,1000+1000*rand(),'20050101') as din ) as d1,
	(select dateadd(minute,2000+1000*rand(),'20050101') as dout ) as d2

-- generate intervals table (hours)
create table #interval(hr int)
insert #interval(hr) select min(datediff(hour,0,timein)) from #period
while (select max(hr) from #interval) < (select max(datediff(hour,0,timeout)) from #period )
	insert #interval(hr) select max(hr)+1 from #interval

-- calculate the frequency per hour
select
	convert(varchar(19),dateadd(hour,hr,0),120)
	+ ' - '
	+ convert(varchar(19),dateadd(second,-1,dateadd(hour,hr+1,0)),120) as interval,
	count(*) as frequency
from
	#interval
	join #period on datediff(hour,0,timein) < hr+1 and datediff(hour,0,timeout) >= hr
group by
	hr
order by
	hr


drop table #period
drop table #interval

Edit: mistake in red
rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-02 : 18:32:21

TG, hmmmm, ok it would be nice to have that table "Usage".....
BUT consider the following:
The job that collects the data is for some reason not run for a time.
during that time we will have no statistics !

eg. not run during 12:00 - 16:00 one day, how many concurrent users 13:00-15:00 that day?

Edit: also it might not give the correct result, approx but not correct.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-02 : 18:54:35

This might be good to know:
When You are comparing 2 timeintervals, to see if one is within the other.
I1 : a0->a1
I2 : b0->b1
~~To determine if I2 is within I1, then the following apply:~~
Does I1 and I2 overlap ?
(b0 <= a1) and (b1 >= a0) -- use equalities where applicable.

That correlation can be used in a nonequi-join between tables with timeintervals.

rockmoose

Zathras
Starting Member

28 Posts

Posted - 2005-03-02 : 22:04:56

Oh wow, you guys are great.... give me a day to go through this... Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources