>>>OK, still on the last-minute panic:
>>>
>>>I have a table where each rec has a start_date and end_date. These fields indicate the currency of the records.
>>>If either is blank then there's no date bound,
>>>
>>>e.g blank start_date but there is an end_date means: "This rec is current from the beginning of time until the end date"
>>>e.g blank end_date but there is an start_date means: "This rec is current from the the given start date until the end of time"
>>>e.g. both blank means: There are no date bounds on this rec.
>>>
>>>I need to eliminate recs with dupe key, getting only the most recent vals, and assume a non-blank start_date is more recent than a blank one. Can't get my head round that today cos of all the other pressures. Pleas ehelp.
>>>
>>>'ppreciate it
>>>
>>>Terry
>>
>>For whole table or for some groups?
>
>I just want to filter the whole table according to my start-prev-period date and end-current-period date, so I don't have more than one record for an operator-route key combination.
>
>>Also can you have non blank Start date but blank end date?
>
>Yes, and vice versa, all as laid out above. But a valid start_date is more recent than a blank one. I'm only interested in the stat dates here:-)
Try first this (I am not sure if this will works on VFP7)
SELECT * FROM MyTable;
INNER JOIN (SELECT Operator, Route, MAX(Start_Date) AS Start_Date;
FROM MyTable;
GROUP BY Operator, Route) Tbl1;
ON MyTable.Operator = Tbl1.Operator AND MyTable.Route = Tbl1.Route AND MyTable.Start_Date = Tbl1.Start_Date;
INTO CURSOR SupposedNonDups
If this not works, try:
SELECT * FROM MyTable;
WHERE STR(Operator)+STR(Route)+DTOS(Start_Date) =;
(SELECT STR(Operator)+STR(Route)+DTOS(MAX(Start_Date)) FROM MyTable Tbl1 WHERE Tbl1.Operator = MyTable.OIperator AND;
Tbl1.Route = MyTableRoute);
INTO CURSOR SupposedNonDups
If this didn't works either:
SELECT Operator, Route, MAX(Start_Date) AS Start_Date;
FROM MyTable;
GROUP BY Operator, Route;
INTO CURSOR Tbl1
SELECT * FROM MyTable;
INNER JOIN Tbl1 ON MyTable.Operator = Tbl1.Operator AND;
MyTable.Route = Tbl1.Route AND;
MyTable.Start_Date = Tbl1.Start_Date;
INTO CURSOR SupposedNonDups
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.