WOT: SQL query help needed again ... Options

[SirAndy]

it's time for another SQL question ...

here's the problem:

i have a table with event log information. it has a unique id for each entry (primary key), a user id, timestamp and other info.

getting the info out of the table is easy except for one particular way.

what i need is a page where i list all the last actions taken by any user per day.
meaning, if users xyz created 30 logentries today, i only want to show the latest for any given day.
so, the list should contain exactly ONE entry for each distinct user per day, showing his/her latest action ...

something like this, except, the query below doesn't work ... (IMG:style_emoticons/default/rolleyes.gif)

CODE

SELECT * FROM tbl_EventLog WHERE iID IN (

  SELECT DISTINCT el.iUserID, dummy.iID FROM tbl_EventLog as el
  INNER JOIN tbl_EventLog as dummy ON dummy.iUserID = el.iUserID
  WHERE (el.dtCreated >= '3/26/2007 0:00:01 AM')

)

anyone?
(IMG:style_emoticons/default/confused24.gif) Andy

[Air_Cooled_Nut]

Can you use a MAX clause on the date/time stamp? That would probably eliminate the need for the DISTINCT clause.

[SirAndy]

Can you use a MAX clause on the date/time stamp? That would probably eliminate the need for the DISTINCT clause.

i don't see how using MAX on the date/timestamp would yield a resultset with only one entry per unique user ...
(IMG:style_emoticons/default/idea.gif) Andy

[fiid]

(IMG:style_emoticons/default/agree.gif) He's right - we have to do something similar in our software - you do max(date) where date is less than the end of the day you're interested in.

[fiid]

I think you might group by userid - but not sure....

[blitZ]

The Max(dtCreated ) would probably work, then group by user and log ID. You will then have the IDs you need to get the details you require.

[SirAndy]

The Max(dtCreated ) would probably work, then group by user and log ID. You will then have the IDs you need to get the details you require.

that's what i was trying to avoid ... i hate looped queries. i'm looking for a single query that will give me a single resultset to work with.

for now, i have created a nested loop (yuk (IMG:style_emoticons/default/barf.gif) ) and that works, but it seems like there should be a better way ...

here's what i have right now (works, but is ugly (IMG:style_emoticons/default/icon8.gif) )

CODE

query1 =
SELECT DISTINCT iUserID FROM tbl_EventLog
WHERE dtCreated >= '03/26/2007 00:00:01 AM'
ORDER BY iUserID

do while not query1.EOF

query2 =
SELECT TOP 1 el.*, iut.sName as sType FROM tbl_EventLog as el    
INNER JOIN tbl_INFO_UserTypes as iut ON iut.iID = el.iUserType
WHERE (el.iUserID = query1.iUserID) AND (el.dtCreated >= '03/26/2007 00:00:01 AM')
ORDER BY el.dtCreated DESC

... do something here with result ...


query1.MoveNext
loop

anything better would be good. anyone?
(IMG:style_emoticons/default/pray.gif) Andy

[drewvw]

Not sure what DB you are using, but any thoughts on dropping that into a stored proc?


that way you can abstract the looping to the DB layer and return a single result set. Performance would probably be better as well.

[SirAndy]

Not sure what DB you are using, but any thoughts on dropping that into a stored proc?
that way you can abstract the looping to the DB layer and return a single result set. Performance would probably be better as well.

yeah, i could. MS-SQL 8 Server.
problem is, the server app is HUGE and was entirely done in ASP without a single stored procedure. it's all inline ASP queries.

if i start using stored procedures now, i'm afraid i'll confuse all the other programmers even more than they already are.
we're talking hundreds of thousands of lines of code ...
(IMG:style_emoticons/default/rolleyes.gif) Andy

[SirAndy]

plus, i just noticed, with my hacked solution, i can't sort the result by date ...

(IMG:style_emoticons/default/headbang.gif) Andy

[drewvw]

yeah, i could. MS-SQL 8 Server.
problem is, the server app is HUGE and was entirely done in ASP without a single stored procedure. it's all inline ASP queries.

if i start using stored procedures now, i'm afraid i'll confuse all the other programmers even more than they already are.
we're talking hundreds of thousands of lines of code ...
(IMG:style_emoticons/default/rolleyes.gif) Andy

That's a really good point. A lot of the legacy ASP code where I work is a mix of overly complex stored procs and inline queries. Its a mess for others to figure out (READ: me the last 2 months).


Do we have any true DBAs in the club or just programmers who dabble in DB?

[watsonrx13]

Andy, what's the fields that you're trying to return?

If you only wanted the userid and date/time then....

Select MAX(date/time),userid
from xxxx
where date/time between fromdate and todate
group by userid;

BTW, I'm a PL/SQL Oracle developer....

-- Rob

[SirAndy]

Do we have any true DBAs in the club or just programmers who dabble in DB?

no real DBA's here ... this is just a project i'm doing working as a contractor. they brought me in for the project because nobody here inhouse can handle the scope ...

lots of work but good money and otherwise easy. i do easier SQL all day long, it's the more complicated queries where my lack of edufication shows.

should have taken those damm classes 20 years ago. went drinking instead ...
(IMG:style_emoticons/default/biggrin.gif) Andy

[drewvw]

I am the same way....I know enough DB/SQL to be dangerous but its not my strength by a long shot.

I can ask one guy I know tomorrow who might have a nifty solution for this....

[SirAndy]

Andy, what's the fields that you're trying to return?

If you only wanted the userid and date/time then....

Select MAX(date/time),userid
from xxxx
where date/time between fromdate and todate
group by userid;

BTW, I'm a PL/SQL Oracle developer....

-- Rob

yeah, thought of that but won't work. i need all the columns in the table for each result.
the problem with "GROUP BY" as well as "DESTINCT" is that you can't have any other fields returned unless you add them to the "GROUP BY".
which renders the query useless ...
(IMG:style_emoticons/default/headbang.gif) Andy

PS: what i need:

- go through all rows in table tbl_EventLog
- find all entries from today
- narrow down to last entry for each user
- sort by time (DESC)

each user has many entries in the event log every day, but the list should only show their latest activity ...

the output will look like this (note that the list *should* be sorted by time, but it isn't right now):

[SirAndy]

well, at least i got the list ordered by time now ...

still not pretty, looping queries and all, but it works, i guess ...
(IMG:style_emoticons/default/dry.gif) Andy

CODE

SELECT iUserID, max(dtCreated) as dtMax FROM tbl_EventLog
GROUP BY iUserID
ORDER BY dtMax DESC

this gives me a initial resultset with all the user ids sorted by date. i now can loop through all of these and find the latest entries for the day ...

[blitZ]

Hmm, wouldn't something like this work?


SELECT *
FROM tbl_EventLog
WHERE iID IN (

select iID from (
SELECT iUserID, iID, max(dtCreated) as lastEntry
FROM tbl_EventLog
WHERE dtCreated >= '3/26/2007 0:00:01 AM'
group by iUserID, iID ) dTable )

order by dtCreated

[SirAndy]

Hmm, wouldn't something like this work?


SELECT *
FROM tbl_EventLog
WHERE iID IN (

select iID from (
SELECT iUserID, iID, max(dtCreated) as lastEntry
FROM tbl_EventLog
WHERE dtCreated >= '3/26/2007 0:00:01 AM'
group by iUserID, iID ) dTable )

order by dtCreated

no suck luck ... (IMG:style_emoticons/default/sad.gif)
tried it, returns the full set of 4000+ entries in the eventlog for today.

keep 'em coming ...
(IMG:style_emoticons/default/type.gif) Andy

[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.

[SirAndy]

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 ...