SQL Statement (ORACLE)

moochie

Hi all,

I am having trouble tyring to generate a sql query. This is probably down to the design of the schema by this is something i CANNOT alter. My Schema is as follows:

CREATE TABLE sysevent (
index NUMBER(10,0) NOT NULL, --a unidue index
lastupdate DATE NULL, -- oracle date
eventid NUMBER(10,0) NULL, -- id of an event. Mapped to another table
user VARCHAR2(1024) NULL -- user that performed event
)

Events get logged to this table for different events in the system but the ones i am interested in are the user login(1000) and logoff(2000) events(event id in braces). So what i need is a SQL statement that will return a list of users that were on the system between two time variants.

Things to remember is that users could log in and log off as they wish means the table will cantain many logon's / logoff's for a particular user. I am only interested in the user logged in at some point during that hour.

I apologise i don't have some basic query written because i'm struggling to get my head around the overall scenario.


Help Pls!

Rich

Smoggy

I have no idea about ORACLE SQL, but try something like this :

Select user from sysevent
Where lastupdate between @StartHour and @EndHour
And eventid = (1000)

This will return all users who have logged on between the specified time period.

Edit : This might return the same user multiple times , if this is the case you might need to use a statement like Unique , Im not sure of the extact syntax, but i tis something like :

Select Unique user from sysevent
Where lastupdate between @StartHour and @EndHour
And eventid = (1000)

Tom Dunne

Sounds like the following:

SELECT user, lastupdate
FROM sysevent
WHERE eventid=1000 or eventid=2000
AND lastupdate between to_date('01-AUG-2006 11:00', 'DD-MMM-YYYY HH:mm') and to_date('01-AUG-2006 11:59', 'DD-MMM-YYYY HH:mm')

which should return all usernames that logged event 1000 or 2000 between 11:00am and 11:59am on the 1st of August.

moochie

Thanks guys. I have that. Will use that to_date() function though.

So the big question getting me is how now do i handle a user that has logged in before the lowest time specified and does not log out until after the upper limit specified??

I will need to include that user too because he is considered active during that time.

Thanks again

Rich

Smoggy

eventid=1000 or eventid=2000

will do what your looking for , as if they either login OR logout during that period it will return the data your looking for.

Edit : I just re read ur statement and if they login before your lowest and out after ur latest time. Then it wont return the data.

moochie

What if they login a minute before the time specified in the query and don't logout until after? No event.. I will need a lot more detail in the query to check users that are logged in before the time and have not logged out at the end?

Rich

Tom Dunne

Can you do a query over 24 hours? That way, you will always catch people logging in/out.

The other alternative is to get a list of usernames and run a query on each of them (not the most efficient, especially if you have loads of users).

Smoggy

If you know the login and logout times you can flip the statement on its head and test to see if the times your testing for fall within the login / out period

originally :
where (Login between Hour1 and Hour2 ) or (Logout between Hour1 and Hour2)

new :
where (Hour1 between Login and Logout) or (Hour2 between Login and Logout)

bonkey

Hmmm.

Not as easy as it might seem.

If I understand what you're logging, the lastupdate is the time a given event was logged. Also, I'm assuming that if a user logs in, then out, then in again, there are three records added (i.e. that each event gets logged, and you don't just record the last instance of an event.)

If these assumptions are correct, then you first want a list of all users who were logged in before the end-date. You then want to exclude from this any users who were logged out before the start date. What are left are all users logged in before the start date, and were still logged in for at least some of the period, as well as all users who logged in during the period in question.

This isn't particularly easy. From what I can see, you won't be able to avoid using nested queries as "virtual" tables.

I've had a stab at it below. I've used quite verbose names to try and make it clear what I'm doing.

Some explanation follows the code.

select  logged_in_before_end.user
     ,  logged_in_before_end.login_date_and_time
  from  (
           select   user
                ,   max(lastupdate) as login_date_and_time
             from   sysevent
             where  eventid = 1000        -- login
               and  lastupdate < TO_DATE ([b]<enddate>[/b], 'dd-mm-yyyy hh24:mi:ss')
           group by user    
        ) as logged_in_before_end
  left outer join (
           select   user
                ,   max(lastupdate) as logout_date_and_time
             from   sysevent
             where  eventid = 2000        -- logout
               and  lastupdate < TO_DATE ([b]<startdate>[/b], 'dd-mm-yyyy hh24:mi:ss')
           group by user    
             ) as logged_out_before_start
  on  logged_in_before_end.user = logged_out_before_end.user
  and logged_in_before_end.login_date_and_time > logged_out_before_end.logout_date_and_time 

The first nested query gives me every user who ever logged in before the end date, and the last time/date they logged in before said date.

The second nested query gives me every user who ever logged off before the start date, and when they logged off.

For each record in the logged-in group, we can say the following :

1) If there is no matching record in the logged out-group, the user is logged in during the time period.
2) If there is a matching record in the logged-out group, but the logout occurred before the login, then the logout refers to a previous "session" of the user, and the user is logged in during the time period.
3) If there is a matching record in the logged-out group, and the logout occurred after the login, then the user had logged out before the time-period in question started.


The use of an OUTER JOIN (I'm assuming Oracle 9i or newer) means records in situation 1 are included.
The user of a > in the second part of the ON clause for the join means records in section 2 are included, and section 3 are excluded.

I''ve used <stuff like this> where there's some value you'd need to specify.

Smoggy

Real nice piece of SQL.

bonkey

Dreadful from an optimisation perspective though...at a guess.

Still...a correct answer is generally the main prerequisite. Producing the wrong answer quickly is usually not what you want

sh_o

bonkey wrote:

Dreadful from an optimisation perspective though...at a guess.

Just curious - how would it be bad from an optimisation perspective? Any suggestion on how to improve it?

Smoggy

I also thought that , but didn't dare ask

bonkey

Mostly because the LOJ is performed on two "virtual tables", each of which is using an aggregation funtion.

So the two virtual tables will be calculated and stored in memory, then a "brute force" join-strategy will be used (compare every row to every row) cause there's no indexes to help short-cut things at join-time.

jc