 WOT: SQL query help needed again ... |
|
|
|
Porsche, and the Porsche crest are registered trademarks of Dr. Ing. h.c. F. Porsche AG.
This site is not affiliated with Porsche in any way. Its only purpose is to provide an online forum for car enthusiasts. All other trademarks are property of their respective owners. |
|
|
| SirAndy |
  Mar 26 2007, 04:41 PM
Post
#1
|
|
Resident German  Group: Admin Posts: 42,244 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California  |
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 |
   |
 
|
  |
Replies
| ClayPerrine |
Mar 26 2007, 08:47 PM
Post
#2
|
|
Life's been good to me so far..... Group: Admin Posts: 16,422 Joined: 11-September 03 From: Hurst, TX. Member No.: 1,143 Region Association: NineFourteenerVille 
|
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 |
Mar 26 2007, 09:00 PM
Post
#3
|
|
Resident German Group: Admin Posts: 42,244 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
QUOTE(ClayPerrine @ Mar 26 2007, 06:47 PM)  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 ... |
|
|
|
Posts in this topic
SirAndy WOT: SQL query help needed again ... Mar 26 2007, 04:41 PM
Air_Cooled_Nut Can you use a MAX clause on the date/time stamp? ... Mar 26 2007, 05:14 PM
SirAndy
Can you use a MAX clause on the date/time stamp? ... Mar 26 2007, 05:24 PM fiid :agree: He's right - we have to do something s... Mar 26 2007, 05:25 PM fiid I think you might group by userid - but not sure..... Mar 26 2007, 05:25 PM blitZ The Max(dtCreated ) would probably work, then grou... Mar 26 2007, 05:26 PM SirAndy
The Max(dtCreated ) would probably work, then gro... Mar 26 2007, 06:01 PM drewvw Not sure what DB you are using, but any thoughts o... Mar 26 2007, 06:10 PM SirAndy
Not sure what DB you are using, but any thoughts ... Mar 26 2007, 06:29 PM drewvw
yeah, i could. MS-SQL 8 Server.
problem is, the ... Mar 26 2007, 06:42 PM SirAndy
Do we have any true DBAs in the club or just prog... Mar 26 2007, 06:53 PM SirAndy plus, i just noticed, with my hacked solution, i c... Mar 26 2007, 06:31 PM watsonrx13 Andy, what's the fields that you're trying... Mar 26 2007, 06:52 PM SirAndy
Andy, what's the fields that you're tryin... Mar 26 2007, 07:02 PM drewvw I am the same way....I know enough DB/SQL to be da... Mar 26 2007, 06:59 PM SirAndy well, at least i got the list ordered by time now ... Mar 26 2007, 07:38 PM blitZ Hmm, wouldn't something like this work?
SEL... Mar 26 2007, 08:09 PM SirAndy
Hmm, wouldn't something like this work?
SE... Mar 26 2007, 08:22 PM ClayPerrine
Andy,
Look up a tool on Microsoft's website... Mar 26 2007, 09:46 PM bigkensteele I have kicked this around for a half hour, and I t... Mar 26 2007, 10:18 PM SirAndy
I have kicked this around for a half hour, and I ... Mar 26 2007, 10:29 PM drewvw good response...avoiding IN clauses is nice becaus... Mar 26 2007, 10:23 PM |
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:
|
Lo-Fi Version | Time is now: 3rd July 2025 - 02:38 PM |
Invision Power Board
v9.1.4 © 2025 IPS, Inc.