How about something like

dVac = {some date)
SELECT * FROM vacation ;
 WHERE dend = (SELECT MAX(dend) FROM vacation WHERE dend >= dVac);
 INTO CURSOR curTemp

? IIF(curTemp.dstart <= dVac, curTemp.dstart-1,dVac)

This returns the original date if it is not in the vacation period. You may have to tweak it for a different value if that is not what you need. You also need a final record where dEnd is very large to cover the case when the input date is past the last vacation date in the table.

HTH.........Rich



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