Hello,

I have a table with a primary key consisting of flight and time. Some time
ranges don't have data, How can I return the nearest record before or after
a given flight and time?

Thanks

Marc Pelletier
Goldak Airborne Surveys

Marc Pelletier <marc@stopspam.goldak.ca> wrote on Mon, 7 Aug 2006 17:43:25 -0400

>Hello,
>
>I have a table with a primary key consisting of flight and time. Some time
>ranges don't have data, How can I return the nearest record before or after
>a given flight and time?
>
>Thanks
>
>Marc Pelletier
>Goldak Airborne Surveys
Marc


I don't think you can do it with parameters, but if you build it on the fly then something like this:-

with MyQuery do  // I've assumed FlightTime is stored in minutes e.g. 9am = 540
begin
 Close;
 SQL.Clear;
 SQL.Add(SELECT FlightNo, FlightTime,'');
 SQL.Add('      ABS(FlightTime - ' + IntToStr(RequiredDeparture) + ') AS TimeDiff,');
 SQL.Add('      IF(FlightTime < ' + IntToStr(RequiredDeparture) + ' THEN "BEFORE" ELSE "AFTER")');
 SQL.Add('INTO MEMORY Diffs');
 SQL.Add('FROM Flights');
 SQL.Add(';');
 SQL.Add('SELECT * FROM MEMORY Diffs ORDER BY TimeDiff');
 Open;
end;

Use "TOP 1" to get just the nearest flight or hook the result set onto a grid to see the choices of flights.

HTH

Jeff

P.S. v3 syntax with errors no doubt.

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

"Marc Pelletier" <marc@stopspam.goldak.ca> wrote in message
news:Xns98189EDCFE91Cmmpp1234.dd@64.65.248.118...
> Hello,
>
> I have a table with a primary key consisting of flight and time. Some time
> ranges don't have data, How can I return the nearest record before or
> after
> a given flight and time?

leave index blank for primary key

Table.Filter := FORMATTEDTARGETTIMESTAMP+'>FieldName';
Table.Last
current record is last date/time before target (save as LOWREC)

Table.Filter FORMATTEDTARGETTIMESTAMP+'<=FieldName';
current record is equal or first after target (save as HIGHREC)

if you want to see which is closer, do some datetime math:
Delta1 := TARGET-LOWREC
Delta2 := HIGHREC-TARGET

if Delta1<Delta2 then LOWREC is closer
if Delta2>Delta1 then HIGREC is closer
if they are the same - pick your favorite

-Johnnie

"Johnnie Norsworthy" <jln206@verizon.net> wrote in
news:4236F680-5DCF-4B49-80F3-9388637619D2@news.elevatesoft.com:

> Table.Filter := FORMATTEDTARGETTIMESTAMP+'>FieldName';
> Table.Last
> current record is last date/time before target (save as LOWREC)
>
> Table.Filter FORMATTEDTARGETTIMESTAMP+'<=FieldName';
> current record is equal or first after target (save as HIGHREC)
>

Thanks Johnnie and Jeff, both excellent suggestions. TOP in particular is
what I was looking for.

Cheers

Marc

"Marc Pelletier" <marc@stopspam.goldak.ca> wrote in message
news:Xns98195D1F6A526mmpp1234.dd@64.65.248.118...
> "Johnnie Norsworthy" <jln206@verizon.net> wrote in
> news:4236F680-5DCF-4B49-80F3-9388637619D2@news.elevatesoft.com:
>
>> Table.Filter := FORMATTEDTARGETTIMESTAMP+'>FieldName';
>> Table.Last
>> current record is last date/time before target (save as LOWREC)
>>
>> Table.Filter FORMATTEDTARGETTIMESTAMP+'<=FieldName';
>> current record is equal or first after target (save as HIGHREC)
>>
>
> Thanks Johnnie and Jeff, both excellent suggestions. TOP in particular is
> what I was looking for.

Yeah, that's what I thought about first since this was an SQL group, but
Jeff beat me by two minutes on the response.

And someone searching these newsgroups later has both solutions available -
which is always a good thing.