Last entry before certain date range

[Fherrera]

Hi,

I have a database that has entries, Date and Tag and Value. It is from a Historian and it only recorded values at certain intervals if they where a certain distance away from the previous value, ie. deadband.

In any case, I'm gathering information on a particular tag for a particular time period but for some tag's, there is no information because the data was not updated within that time period (it hadn't changed a significant amount that it met the deadband limit to record a new entry). As such, I'd like to be able to get the LAST entry for a particular tag (last in terms of date) before the time period i'm looking at. Is there a quick SQL statement I can use? I'm thinking something along the lines of MAX might help...

I've tried:

SELECT max(date)
FROM Frank
WHERE DATE < #03/09/1993#;

And this returns the date I want, but not the tag and value (As they aren't aggregate functions it tells me)

Is there a way to modify this so it returns everything I want (date, tag AND value). Or must I do 2 queries. The reason is because any query I run takes atleast 30 miuntes on this access 97 database... (there's too much data...)

Thanks.

Frank

 

[Fherrera]

Hrm.. nevermind I can't believe I didn't think about sub queries but i'm going to try:

SELECT *
FROM FRANK
WHERE DATE IN (
SELECT max(date)
FROM Frank
WHERE DATE < #03/09/1993#);

Which gets the job done in my 10 row sample database, now to try it on the 13million row actualy database...

Does anyone have any other suggestions to do the same thing?

F

 

[PHV]

Have you tried something like this ?
SELECT A.* FROM FRANCK INNER JOIN
(SELECT Max([Date]) As LastDate FROM FRANCK WHERE [Date]<#03/09/1993#) B
ON A.Date=B.LastDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244