multiple count with single record

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

Transact-SQL (2000)

multiple count with single record

Author

Topic

sqluser18
Starting Member

2 Posts

Posted - 2002-09-16 : 22:46:47

i have a table with three columns

CREATE TABLE test (
transid int IDENTITY (1, 1) NOT NULL,
timeIn datetime,
timeOut datetime
)
this table stores the time when a person logs onto the computer, and the logout time.

insert into test (timeIn, timeOut) values ('09/10/2002 10:00:00 AM', '09/10/2002 12:00:00 PM')

My problem is:
i need to retrieve the number of users using the computer by the hour, so according to the above data, at 10am there is 1 user, 11 am - 1 user, and 12pm - 1user
is it possible to build a sql statement to compute this?

thanx.

LW

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-09-17 : 03:40:05

it's late in the afternoon and I'm very tired...but I think it'll be something like this - (I've included some example data)

--create the login data
declare @times table (id int identity, timein datetime, timeout datetime)

insert @times (timein, timeout)
select dateadd(n,5,getdate()), dateadd(n,130,getdate())
insert @times (timein, timeout)
select dateadd(n,-115,getdate()), dateadd(n,45,getdate())
insert @times (timein, timeout)
select dateadd(n,20,getdate()), dateadd(n,180,getdate())
insert @times (timein, timeout)
select dateadd(n,-410,getdate()), dateadd(n,290,getdate())
insert @times (timein, timeout)
select dateadd(n,25,getdate()), dateadd(n,370,getdate())
insert @times (timein, timeout)
select dateadd(n,5,getdate()), dateadd(n,10,getdate())

--create the hours table
declare @counter int
declare @hours table (hour int)
set @counter = 0

while @counter < 24
begin
insert @hours values (@counter)
set @counter = @counter + 1
end

--get number of users per hour
select a.hour, count(b.id)
from @hours a, @times b
where a.hour = datepart(hh, b.timein)
or a.hour = datepart(hh, b.timeout)
or (a.hour between datepart(hh, b.timein) and datepart(hh, b.timeout))
group by a.hour

The trick is that you have to remember that the ones that logged in before and logged out after each hour, were still logged in for that hour.

Let me know if that's it.

Cheerszzzzzzzzz

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-09-17 : 03:42:06

oh - an of course, you'd turn that into a stored procedure, which returns the values for a given date range. etc.

sleep now...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources