Re: How to find first non-vacation day

>> I have a table of vacations
>>
>> create table vacation (
>> id integer primary key,
>> dstart date,
>> dend date );
>>
>>
>> I need to find first non-vacation day before given date.
>>
>> This can be done using the following procedural vfp code
>>
>> function nonvacation( dbefore )
>>
>> for i=dbefore to date(1960,1,1) step -1
>> select vacation
>> locate for between( i, dstart, dend )
>> if not found()
>> return i
>> endif
>> endfor
>> return null
>>
>> but this is very slow
>>
>> How to implement this as sql select statement ?
>
> Haven't given a lot of thought to this, but why not?
>
> SELECT *
> FROM vacation
> WHERE
> dstart < '2006-02-03'
> ORDER BY dstart DESC
> LIMIT 1

Just realized I read the question wrong. The above would give you the
first vacation day...

Maybe alter your table to include all days and add a boolean field to
indicate if it's a vacation day or not? Then you could probably use the
above with some tweaks to the where clause.