Nadya --

Haven't tested this out. This is a combination of code and pseudo code. As I recall from your data model, the alternate key is an intelligent key composed of state, ccode, rcode, town, extryear and extrweek. I am a bit confused regarding the time period of each record. Does it represent a week, or a month? If a record represents a month, then I believe you need a function to convert from the month value given in CalMnth to a range of weeks to get the date value of required month records. Something similar would be needed if each record represents a week -- but you'd get multiple records then.

HTH (but I may be way off base!)

Jay

SELECT * ;
   FROM FilingDate ;
   WHERE NOT EMPTY (CalMnth) ;
   INTO CURSOR TempFDate

SELECT TempFDate.* ;
	from FilingDate ;
UNION
	SELECT FilingDate.*
		INNER JOIN TempFDate ON FilingDate.AKExceptDate = TempFDate.AKExceptDate ;
		WHERE FilingDate.Date < TempFDate.CalMnth ;
		HAVING MAX (FilingDate.Date)
<pre>


>Hi Cecil,
>
>Thanks a lot. So, first select ordered by rcode, town, calmnth descending gives me all not empty months. Now the task is to select 2 first records per each town...
>
>>Nadya,
>>
>>Try Select * from table where !empty(table.calmnth) gives you all that have a date...
>>Try select distinct <townfield>,<fieldlist> from table where Max(table.calmnth) gives you all the maxdate for each <townfield>
>>hope this helps
>>Cecil
>>
>>>Hi everybody,
>>>
>>>I'm trying to figure out a select statement, if it could be done through SQL.
>>>
>>>I have FilingDate table. It has this structure:
>>><pre>
>>>
>>>Structure for table:                   G:\REDP\DBC\SUPPORT\FILINGDATE.DBF
>>>Number of data records:       9669
>>>Date of last update:           01/11/2002
>>>Memo file block size:         1
>>> Code Page:                   1252
>>>         Field     Field Name          Type             Width
>>>             1     DSID                Integer           4
>>>             2     EXTRYEAR            Character         4
>>>             3     EXTRWEEK            Character         2
>>>             4     STATE               Character         2
>>>             5     CCODE               Character         2
>>>             6     RCODE               Character         2
>>>             7     TOWN                Character         4
>>>             8     RECORD_FM           Date              8
>>>             9     RECORD_TO           Date              8
>>>            10     LANDCT_FM           Date              8
>>>            11     LANDCT_TO           Date              8
>>>            12     CALMNTH             Character         10
>>>            13     MDFLAG              Logical           1
>>>            14     NODATA              Logical           1
>>>            15     NOTES               Memo              4
>>>            16     DDFLAG              Logical           1
>>>** Total **                                              70
>>>

>>>
>>>CalMnth is filled, if town/registry is closed, e.g. 062001 indicates, what this town/registry is closed in June, 2001 on ExtrYear = 2001 Week 49 (for example). Otherwise CalMth is empty.
>>>
>>>So, I want to select all max months closed + their data and the prior months closed + their data. I need this info to calculate predicted dates (week) of closing in 122001.
>>>
>>>Could it be done through SQL or I need to create some program to do it?