Subquery, Date, and Time (1 Viewer)

[mtairhead]

Hey, all...

I'm having some difficulty with a subquery. I'm using a subquery to find a previous record's value. It works, with a few exceptions. Here's what I have:

SELECT tblEventLogFile_Clean.Date, tblEventLogFile_Clean.Time, (SELECT TOP 1 previousRecord.Time
FROM tblEventLogFile_Clean as previousRecord
WHERE previousRecord.Time < tblEventLogFile_Clean.Time
AND previousRecord.Date <= tblEventLogFile_Clean.Date
AND previousRecord.PatronID = tblEventLogFile_Clean.PatronID
ORDER BY Date DESC, Time DESC) AS Expr1
FROM tblEventLogFile_Clean;

As you can probably extrapolate with enough scrutiny, these are site event logs. The query I'm building would return the time and date (separate fields) of each event, along with the time (I'll add the date in eventually) of the previous event logged by the previous user.

This query works...mostly. The problem comes when I have a visitor whose time extends from the AM to the PM. Here are some example return values:

+------------------------------------------+
|PatronID | Date | Time |Previous Time|
|------------------------------------------+
|100 |1/1/08|10:00AM |-- (Blank)
|100 |1/1/08|10:05AM |10:00AM
|100 |1/1/08|10:10AM |10:05AM
|200 |1/1/08|11:00AM | 2:45PM
|200 |1/1/08|11:15AM |11:00AM
|300 |1/1/08|11:30AM |--(Blank)
|300 |1/1/08|11:45AM |11:30AM
|200 |1/1/08| 2:45PM |--(Blank)
|200 |1/1/08| 2:50PM | 2:45PM
-------------------------------------------+

The events of patron 100 and 300 are flawless. The results for patron 200 are troubling. Access is returning 2:45PM, a future time, as the closest previous time. My SQL asks for a smaller (or equal) date and a smaller time. Then the patron's 2:45 event returns nothing for the previous time, despite the event logged at 11:15AM. A proper query would return 11:45AM.

Any insight is appreciated.

Thanks,

Andrew

 

[raskew]

Hi -

For starters, you probably need to read-up on how Access stores date/time values:
http://support.microsoft.com/kb/q130514/.

Separating date and time can lead to problems. For example (from the Immediate (debug) window):
Note: If current time = 11:00 AM and previous time = 2:45 PM, then previous time must be from a previous date. I'm assuming the day prior:

x = #1/08/08 11:00 AM#
? cdbl(x)
39455.4583333333

y = #1/07/08 2:45 PM#
? cdbl
39454.6145833333
*******************************************************

Subtracting just the timevalues of x - y leads to:

? timevalue(x) - timevalue
-0.15625

A negative number. Not what we're looking for.

*******************************************************

Now, try x - y:

? x - y
0.84375

Minutes in a day = 24 hours * 60 minutes = 1440

? (0.84375*1440) /60
20.25

--or--

? (0.84375*24)
20.25

i.e. 20 hours 15 minutes

*******************************************************


Hope that helps - Bob