Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
NEAREST record |
Mon, Aug 7 2006 5:43 PM | Permanent Link |
Marc Pelletier | 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 |
Mon, Aug 7 2006 6:22 PM | Permanent Link |
Jeff Cook | 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 |
Mon, Aug 7 2006 6:24 PM | Permanent Link |
"Johnnie Norsworthy" | "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 |
Tue, Aug 8 2006 11:15 AM | Permanent Link |
Marc Pelletier | "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 |
Tue, Aug 8 2006 11:58 AM | Permanent Link |
"Johnnie Norsworthy" | "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. |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |