Re: query trouble
Date: Thu, 07 Sep 2000 23:28:22 GMT
Message-ID: <8p989v$t5j$1_at_nnrp1.deja.com>
>> I'd like a little help with a query that's giving me trouble. I'm
using Oracle 8 on Windows NT as a source for Cognos Impromptu (a
reporting tool). <<
Well that was almost a DDL posting, but you made every non-key column nullable. Can I guess that your table really looks like this (in SQL- 92)?
CREATE TABLE User_Activity
(user_activity_id INTEGER NOT NULL PRIMARY KEY,
login DATE NOT NULL,
logout DATE, -- null means still active???
CHECK (login < logout),
...);
If a NULL logout mean that the session was still active, I have some problems. I decided to use the current timestamp at the time the query is executed as logout.
I also assume that user_activity_id is a unique number that identifies a session, without identifying indiviidual users.
>> I'd like to be able to report the number of users who were, for
example, logged on at 5 pm. So, if someone began a session at 3:12 pm
and ended one at 6:45 pm, I'd like them to be counted as being logged
in at 3 pm, 4 pm, 5 pm and 6 pm. Beyond that, I'd like to report the
count of these users for a given month and a given year. <<
Good luck in escaping the curse of Oracle and getting to a standard SQL.
I hope that you are using the ISO 8601 date and time standards and not this AM and PM crap you show in the samples. After the Y2K thing, you will find that there are a lot of ISO standards -- including SQL -- which are based on 8601 formats.
Her is one solution: first, create an auxilliary table like this:
CREATE TABLE HourlyReport
(period_nbr INTEGER NOT NULL PRIMARY KEY,
start_dateTIMESTAMP NOT NULL,
end_date TIMESTAMP NOT NULL,
CHECK(start_time < end_time));
INSERT INTO HourlyReport
VALUES (1, '1999-01-01 00:00:00.0000''1999-01-01 23:59:59.99999');
etc.
it will be 24 * 365.25 * <<numbetr of years covered>> rows in size, which is pretty small for a decade or two of data.
The query to find the periods in which each activity falls is:
SELECT DISTINCT A1.user_activity_id, period_nbr FROM User_Activity AS A1,
HourlyReports AS H1
WHERE H1.start_date BETWEEN A1.login
AND COALESCE A1.logout, CURRENT_TIMESTAMP)
OR H1.end_date BETWEEN A1.login
AND COALESCE A1.logout, CURRENT_TIMESTAMP)
Notice the DISTINCT!! Without it, you would count both the start and end times of each period.
Now, to answer your original question, tally by periods:
SELECT A1.period_nbr, A1.start_date,
COUNT (DISTINCT A1.user_activity_id) FROM User_Activity AS A1,
HourlyReports AS H1
WHERE H1.start_date BETWEEN A1.login
AND COALESCE A1.logout, CURRENT_TIMESTAMP)
OR H1.end_date BETWEEN A1.login
AND COALESCE A1.logout, CURRENT_TIMESTAMP)
GROUP BY A1.period_nbr, A1.start_date;
--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Sep 08 2000 - 01:28:22 CEST