Nadya,
Take the first and select into new table all not empty months
select maxdate from newtable into tmp1
dele for maxdate
sele maxdate from newtable into tmp2
zap newtable appe from tmp1 then appe from tmp2
hope this helps
>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
, from table where Max(table.calmnth) gives you all the maxdate for each
>>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:
>>>>>>
>>>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?