From: [EMAIL PROTECTED]
> I have a database (mysql 4.0) with radius log entries for each day, we
> receive emails about Acceptable Use Abuses and must figure out exactly
> who was online with a certain IP address when the abuse occurred. As
> you will see below there are multiple starts and stops for any given
> IP address ...
Not sure what does mysql support but I'd try something like
SELECT TOP 1 ID, FullName
FROM the_table
WHERE "IP address" = ?
and Date = ?
and Time <= ?
and "Record Type" = "Start"
ORDER BY Time Desc
to get the start and
SELECT TOP 1 ID, FullName
FROM the_table
WHERE "IP address" = ?
and Date = ?
and Time >= ?
and "Record Type" = "Stop"
ORDER BY Time Asc
to get the end. If the first select doesn't return any row you will
need to requesry the database with something like this:
SELECT TOP 1 ID, FullName
FROM the_table
WHERE "IP address" = ?
and Date = DATEADD( day, -1, ?)
and "Record Type" = "Start"
ORDER BY Time Desc
if the second doesn't return a row you'd use
SELECT TOP 1 ID, FullName
FROM the_table
WHERE "IP address" = ?
and Date = DATEADD( day, 1, ?)
and "Record Type" = "Stop"
ORDER BY Time Asc
It would be enough to find the start, but I think it's better to find
both and compare the FullName just to make sure you found the right
person.
HTH, Jenda
===== [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
