I have a table with a date field: dDate and thousands of records.
The dates are scattered over time including the future. I need to find =
the record with the max date that is =3D< required date.
How would you do this with sql select and also with Locate?
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
On Thu, Mar 12, 2009 at 1:58 PM, KAM.covad
<a_gmail_noSpam@kenmcginnis.com> wrote:
> I have a table with a date field: dDate and thousands of records.
>
> The dates are scattered over time including the future. I need to find the record with the max date that is =< required date.
>
> How would you do this with sql select and also with Locate?
------------------------
Select top 1 <column list>
from yourTable
where dDate <= ldDate
order by dDate descending
For Fox 7 and higher I believe, maybe you need 8??
HTH
--
Stephen Russell
Sr. Production Systems Programmer
Web and Windows Development
Independent Contractor
Memphis TN
901.246-0159
On Thu, Mar 12, 2009 at 8:20 PM, MB Software Solutions General Account
<mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
> On Thu, March 12, 2009 3:09 pm, Stephen Russell wrote:
>> On Thu, Mar 12, 2009 at 1:58 PM, KAM.covad
>> <a_gmail_noSpam@kenmcginnis.com> wrote:
>>
>>> I have a table with a date field: dDate and thousands of records.
>>>
>>>
>>> The dates are scattered over time including the future. I need to find
>>> the record with the max date that is =3D< required date.
>>>
>>> How would you do this with sql select and also with Locate?
>>>
>> ------------------------
>>
>>
>> Select top 1 <column list>
>> from yourTable where dDate <=3D ldDate order by dDate descending
>>
>> For Fox 7 and higher I believe, maybe you need 8??
>
>
> Not sure, but I'm pretty sure TOP 1 was in VFP 5/6? =A0That's pretty basi=
c.
>
SELECT [ ALL | DISTINCT ]
[ TOP nHowMany [ PERCENT ] ]
eColumn1 [ AS ColumnName1 ]
[, eColumn2 [ AS ColumnName2 ] ... ]
FROM [ FORCE ]
[ Database1! ]Table1 [ LocalAlias1 ]
[ [ INNER | LEFT [ OUTER ] | RIGHT [ OUTER ]
| FULL [ OUTER ] ] JOIN
[ Database2! ]Table2 [ LocalAlias2 ]
[ ... ]
[ ON lJoinCondition1 ]
| , [ Database3! ]Table3 [ LocalAlias3 ]
[ ... ] ]
[ WHERE lConditions ]
[ GROUP BY GroupColumn1 [, GroupColumn2 ... ] ]
[ HAVING lGroupFilter ]
[ UNION [ ALL | DISTINCT ] SELECT ... ]
[ ORDER BY OrderCriteria1 [ ASC | DESC ]
[, OrderCriteria2 [ ASC | DESC ] ... ] ]
[INTO CURSOR CursorName [ NOFILTER ]
| INTO TABLE | DBF TableName
| INTO ARRAY ArrayName
| TO FILE FileName [ ADDITIVE ]
| TO PRINTER [ PROMPT ]
| TO SCREEN ]
[ PREFERENCE PreferenceName ]
[ NOCONSOLE ]
[ PLAIN ]
[ NOWAIT ]
In 6
A+
jml
Author: MB Software Solutions General Account
Posted: 2009-03-12 15:20:44 Link
On Thu, March 12, 2009 3:09 pm, Stephen Russell wrote:
> On Thu, Mar 12, 2009 at 1:58 PM, KAM.covad
> <a_gmail_noSpam@kenmcginnis.com> wrote:
>
>> I have a table with a date field: dDate and thousands of records.
>>
>>
>> The dates are scattered over time including the future. I need to find
>> the record with the max date that is =< required date.
>>
>> How would you do this with sql select and also with Locate?
>>
> ------------------------
>
>
> Select top 1 <column list>
> from yourTable where dDate <= ldDate order by dDate descending
>
> For Fox 7 and higher I believe, maybe you need 8??
Not sure, but I'm pretty sure TOP 1 was in VFP 5/6? That's pretty basic.
KAM.covad wrote:
> I have a table with a date field: dDate and thousands of records.
>
> The dates are scattered over time including the future. I need to find the record with the max date that is =< required date.
>
> How would you do this with sql select and also with Locate?
* sql select
* I use 2 statements because the first one will execute really fast without needing
* to pull in irrelevant records. Make sure you have an index on dDate.
* Note the final result could contain multiple records; you didn't specify how to
* determine the *one* record you need other than the specified date.
select max(dDate)
from <table>
where dDate <= ?
into array atemp
dDate = atemp[1]
select *
from <table>
where dDate == m.dDate
* locate
select <alias>
* index on dDate descending tag dDate_desc
set order to tag dDate_desc
set near on
locate for dDate = ?
* you are now on the first record with the date <= to the specified date.
Note my fox is rusty. I see I forgot the semicolons, for instance. So consider this
pseudocode.
Paul
On Thu, Mar 12, 2009 at 8:51 PM, Paul McNett <p@ulmcnett.com> wrote:
> Note my fox is rusty. I see I forgot the semicolons, for instance. So consider this
> pseudocode.
>
As long as you remember your spaces......
A+
jml
I think this will work in VFP9, but I haven't tested it...
SELECT * ;
FROM table ;
WHERE dateField = ( ;
SELECT MAX(date) ;
FROM table t2 ;
WHERE dateField <= ldRequired)
On Thu, Mar 12, 2009 at 14:58, KAM.covad <a_gmail_noSpam@kenmcginnis.com> wrote:
> I have a table with a date field: dDate and thousands of records.
>
> The dates are scattered over time including the future. I need to find the record with the max date that is =< required date.
>
> How would you do this with sql select and also with Locate?
This turned out to be the best for me. Simple, quick and works for all =
dates tested.=20
Select Top 1 * ;
from oFipsCodes ;
where ;
ccode =3D lcFips And ;
!EMPTY(dbgdate) AND ;
dbgdate <=3D ldDate ;
order By dbgdate Descending
scatter oFipsCodes
nTaxRate =3D oFipsCodes.nTax
This is useful since many counties in California are increasing their =
sales taxes on 4/1. This allows us to enter the new tax rate now and =
have it automatically apply for transactions beginning with 4/1.
----- Original Message -----=20
From: Stephen Russell=20
To: ProFox Email List=20
Sent: Thursday, March 12, 2009 12:09 PM
Subject: Re: VFP9 SP1 question for sql guru
On Thu, Mar 12, 2009 at 1:58 PM, KAM.covad
<a_gmail_noSpam@kenmcginnis.com> wrote:
> I have a table with a date field: dDate and thousands of records.
>
> The dates are scattered over time including the future. I need to find =
the record with the max date that is =3D< required date.
>
> How would you do this with sql select and also with Locate?
------------------------
Select top 1 <column list>
from yourTable
where dDate <=3D ldDate
order by dDate descending
For Fox 7 and higher I believe, maybe you need 8??
HTH
--=20
Stephen Russell
Sr. Production Systems Programmer
Web and Windows Development
Independent Contractor
Memphis TN
901.246-0159
[excessive quoting removed by server]
sry,
scatter name oFipsCodes
----- Original Message -----=20
From: KAM.covad=20
To: ProFox Email List=20
Sent: Saturday, March 14, 2009 10:15 AM
Subject: Re: VFP9 SP1 question for sql guru
This turned out to be the best for me. Simple, quick and works for all =
dates tested.=20
Select Top 1 * ;
from oFipsCodes ;
where ;
ccode =3D lcFips And ;
!EMPTY(dbgdate) AND ;
dbgdate <=3D ldDate ;
order By dbgdate Descending
scatter name oFipsCodes
nTaxRate =3D oFipsCodes.nTax
This is useful since many counties in California are increasing their =
sales taxes on 4/1. This allows us to enter the new tax rate now and =
have it automatically apply for transactions beginning with 4/1.
----- Original Message -----=20
From: Stephen Russell=20
To: ProFox Email List=20
Sent: Thursday, March 12, 2009 12:09 PM
Subject: Re: VFP9 SP1 question for sql guru
On Thu, Mar 12, 2009 at 1:58 PM, KAM.covad
<a_gmail_noSpam@kenmcginnis.com> wrote:
> I have a table with a date field: dDate and thousands of records.
>
> The dates are scattered over time including the future. I need to find =
the record with the max date that is =3D< required date.
>
> How would you do this with sql select and also with Locate?
------------------------
Select top 1 <column list>
from yourTable
where dDate <=3D ldDate
order by dDate descending
For Fox 7 and higher I believe, maybe you need 8??
HTH
--=20
Stephen Russell
Sr. Production Systems Programmer
Web and Windows Development
Independent Contractor
Memphis TN
901.246-0159
[excessive quoting removed by server]