Query problem

[virtualranger]

I have two tables with a one to many relationship. The first table ('link') contains a list of unique links between tenants and properties. The status of these links can be changed by the user (e.g from 'viewing' to 'tenant'). When the status changes it updates the record in the link table with the new status and puts an entry in the second table ('history') with the link reference, status, user_id, and the date. Thus we have a historical record of the link status.

Hope that makes sense!

For every record in the link table I want to extract only the last (most recent) record in the history table. I'm not sure how to do it. Any ideas, or will you need more info on table structures?

Thanks,
Jamie

 

[DSummZZZ]

I would make an index tag on a date + time field and order it descending.
If you don't have date/time fields, you wil just about have to start at the bottom of the file and scan backwards in natural order.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[JRB-Bldr]

You must be using an Index on the 'Child' table so that you can support the Parent/Child Relation.

I assume that this 'Child' Index expression does not currently contain anything about Date.

As Dave suggests above you will need to include some Date reference within your 'Child' table Index expression.

However, since the other Index expression parameters are most likely not Date fields, you will have to get creative in how you utilize the Date information.

An Index expression such as:
INDEX ON CharField + PADL(ALLTRIM(STR(CTOD("12/31/2099")-ThisDate)),10,"0") TAG Key
or such will yield an index retaining the original link character field expression and also include Date info in descending order.

Also note that you will most likely have to SET EXACT OFF when you establish the RELATION so that the 'Parent' table may find records within the 'Child' table without an EXACT expression match.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[virtualranger]

Thanks guys.

Thanks,
Jamie

 

[1oldfoxman]

I'm not sure what version you're working with, but if it supports SQL, you could try something like:

select Link.LinkRef, max(History.Linkdate) ;
from Link,History;
where History.LinkRef = Link.LinkRef ;
group by Link.LinkRef

I honestly can't remember what version first introduced SQL capabilities, so if it was after v2.6, my suggestion is useless.

 

[dbMark]

Definitely FoxPro 2.6 has SQL language. It was such a great solid release that it's still going after all this time, just as Visual FoxPro 6 SP5 was another landmark version in the Visual series.

 

[severach]

There are lots of great ways to do this depending on the specifics, how big are the files, will you be doing this by hand as a one time quick fix or programmatically, how does ONE indicate the proper record in TWO, how easy is the latest date to identify?

* LASTREC will be storing a recno(), 8 allows for 10M possible TWO records
CREATE TABLE ONE (KEY C(8),LASTREC C(8)) && KEY UNIQUE
* Since you are adding records over time, I'll assume that the lowermost record is the latest dated record you want
CREATE TABLE TWO (KEY C(8),DT D) && KEY NOT UNIQUE
CLOS DATA
* I don't see why you want to do this with all ONE.records but here goes
USE ONE
INDEX ON KEY UNIQUE TAG KU
SET ORDER TO KU
SELE 0
USE TWO
SET RELA TO KEY INTO K1
*TWO is in unsorted order so the lowest record is the one to keep
*Also, since I am using the relation backwards as many-to-1, I do not use SET SKIP
REPL FOR NOT EOF("ONE") ONE.LASTREC WITH RECNO()
* Notice that ONE.LASTREC will be overwritten many times with only the LAST-LOWEST aka the latest date one being preserved. Whether or not this is too sloppy depends on how big your files are and how often this is done.
USE
SELE ONE
DELE TAG ALL
CLOSE

If you can't depend on the order of the files and SQL appeals to you, a simple select can obtain the recno() of the greatest DT. Examine the SUBSTR part carefully to see how a MAX() can be assembled and shredded to provide exactly and only what we want. FOX SELECT does not provide the HAVING MAX(...) function extension so we must duplicate it from what is provided.

USE ONE
FL=LEN(LASTREC) && You can't include LEN(LASTREC) in the SELECT statement without generating an unintentional JOIN. You can use a memory variable though.
USE
SELE KEY,SUBSTR(MAX(DTOC(DT,1)+STR(RECNO(),FL)),9) AS MAXREC FROM TWO GROUP BY KEY INTO CURSOR TWOMAX
* FOX does not always handle RECNO() correctly during SELECT..GROUP BY. Check carefully and switch to a real field if necessary
INDEX ON KEY UNIQUE TAG KU
SELE 0
USE ONE
SET RELA TO KEY INTO TWOMAX
REPL ALL LASTREC WITH IIF(NOT EOF("TWOMAX"),VAL(TWOMAX.MAXREC),0)
*LASTREC gets 0 if there are no entries in TWO
CLOS DATA