Thanks, Jay. Ok, while I was thinking about this problem and asking my colleague, what she really wants, the definition of the problem is changed.

Here is what I need to produce:
FilingDate table contains filing dates for CT, MA, RI one record per each week.

For CT & RI data are collected by town level, for MA - by registry level, so town field is empty.

So, say, on week 2001 40 we may have:

CT Registry1 Town1 DateFrom1 DateTo1
CT Registry1 Town2 -         -       && We don't have Filing Dates for this town this week
CT Registry1 Town2 DateFrom1' DateTo1' && Could be different, than for town1  
etc.

Now, I need to give the info about how many Filing Dates we have per registry per week. It means, I need to find Max DateTo and Min DateFrom per each registry per week and calculate number of dates between them excluding Holidays and WeekEnds.

Well, I've already written a procedure, which does something similar. So, I need to take this procedure and adapt it for this particular task.
Quite trivial after all.







>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?