max concurrent record query

I was having a problem I was hoping some SQL Wizard could help with: We log our dial-up access statistics into an Oracle database, and then I write a couple of reports on them. There are 2 tables: a start_session and an end_session. Each table has a time and a unique session ID that is assigned by the modem server, so because each occurrance of logging on and logging off gets it's own log record, it was easier to put the records into 2 tables and then join them with the unique modem server-generated ID. Anyways, here's the gist of the problem: one of the requests that came in is to know what the maximum number of users that were logged in at any one time, and how many were there.

I was thinking I could do it in a procedure that loops through a day (starting at midnight) at 5 minute intervals and does a count on number of records where loop_time between start_time and end_time, and increment a counter by 1, and store the max in some variable. But my senses tell me that this can't be the best way of doing this, because it doesn't seem clean to me at all.

I'd rather do it in straight SQL and not PL/SQL, but I'll take any solutions that anyone has for solving this query. I figure someone else must transfer log files into Oracle and had to run a similar report, but even if not, someone out there has a lot better idea than mine.

• Scott

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 22 1999 - 10:17:16 CDT