Nadya --
I think you've got it!
Have a good weekend.
Jay
>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 - -
>CT Registry1 Town2 DateFrom1' DateTo1'
>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?
