WOT: SQL query help needed again ... Options

[ClayPerrine]

Andy,
Look up a tool on Microsoft's website called "eventcombMT". It will parse the event logs on any server, and return them by date, type and user, or any other field. It also doesn't require a table be imported to SQL, just query them live. Embed the app in a stored procedure, and feed it the proper switches.

this is *NOT* a windows event log !!!

it's just a DB table called tbl_EventLog and has absolutely NOTHING to do with the OS eventlog!

it logs events of peoples (users) interaction with the B2B website ...
(IMG:style_emoticons/default/bye1.gif) Andy

PS: it's *NOT* a webserver log either! this is a completely proprietary format used by the B2B website to track specific events on the user pages.
it's populated by the ASP code and i need to build a page in the admin section that allows to break down the log by user/date ...

OK...OK... I am suitably chastised... I thought you were pulling in the windows event log and then searching it with a SQL query.

[bigkensteele]

I have kicked this around for a half hour, and I think that the most efficient way to do it would be a to create a work table using something like this:

SELECT xxx.user, xxx.date, Max(xxx.time) AS MaxTime INTO WORKTABLE
FROM xxx
GROUP BY xxx.user, xxx.date
HAVING xxx.date = 03/26/2007;

Then do a join of table xxx and WORKTABLE using user, date, and time = MaxTime.

SELECT xxx.*
FROM WORKTABLE INNER JOIN xxx ON (WORKTABLE.user = xxx.user)
and (WORKTABLE.date = xxx.date)
and (WORKTABLE.MaxTime = xxx.time);

With the above solution, I would create an additional index on your table over the user, date and time columns.

However, if your primary key is a sequentially assigned numeric, you could include that in the first query as

SELECT Last(xxx.key) AS LastKey, xxx.user, xxx.date, Max(xxx.time) AS MaxTime INTO WORKTABLE
FROM xxx
GROUP BY xxx.user, xxx.date
HAVING xxx.date = 03/26/2007;

and then your second query would only need to be

SELECT xxx.*
FROM WORKTABLE INNER JOIN xxx
ON (WORKTABLE.LastKey = xxx.key);

Hope this helps, please let us know what you end up doing.
BKS

[drewvw]

good response...avoiding IN clauses is nice because they are pretty expensive.

[SirAndy]

I have kicked this around for a half hour, and I think that the most efficient way to do it would be a to create a work table using something like this:

hmmm, i thought about using a temp table as well. that might work ... (IMG:style_emoticons/default/idea.gif)

too bad Microsoft never got over the fact that "DISTINCT ON" is not part of the SQL standard.
"DISTINCT ON" let's you specify multiple columns to return but the distinct clause is only used on the column you specify with the ON param ...

really too bad MS does not support it. but many others don't either. would have been nice to have, as it would solve my problem and let me get away with just one simple query ...

for now, i'm doing to two nested queries in the code. it works fine and speed is not an issue here as it is in the admin section of the B2B site.
the page only gets maybe 30 hits a day ...

thanks for all the input guys!
(IMG:style_emoticons/default/pray.gif) Andy