[thelist] Sql query help
Luther, Ron
ron.luther at hp.com
Wed Aug 25 13:08:10 CDT 2004
Robert Hanson noted that:
>>Sarah Sweeney writes:
>>My question is, how do I select only those records from my stats table
>>that have a timestamp of at least 4 hours more than the previous record
>>for the client. I'm thinking something along these lines...
... and then offered up a tricky sequence of steps to solve this all in SQL.
Hi Robert || Sarah,
First of all, I think I agree with Robert ... I'm not quite sure you've
presented exactly what you are looking for. (The reason I say that is
because if your 'stats table' contains ... oh, let's say 6 months of data,
then you could easily have 20 records in there meeting this criteria for
one user and only 1 meeting this criteria for another user - even if each
had 100 records in the table to begin with.) Now, if that's really what
you want as output then I think I would write a simple SQL statement to
sort the data by timestamp and put that recordset through a loop in some
programming language to do the heavy lifting and timestamp comparison.
Write the criterion matching records out to a second table and do a
simple 'select and display' on the simplified table.
So let's talk about it on a 'go forward' basis instead ... allow me to
change your application design (generous eh?) and add a new table called
"four_hour_stats". Now, when a client hits your application (1) log that
hit in your normal 'stats' table, and (2) compare the current time to
the 'most recent prior access' [this part involves SQL with a MAX(fld)
in it] ... if the time differential is greater than 4 hours, then log
this as a record in your shiny new "four_hour_stats" table. Lather, rinse,
repeat. That will give you a 'go forward' table built *only* with records
from clients with a greater than 4 hour delta in access times.
HTH,
RonL.
More information about the thelist
mailing list