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

Your code found the last non-vacation day before given date

this found first

CREATE CURSOR vacation  (dstart D,dend D)
INSERT INTO vacation  VALUES (DATE()+3,DATE()+5)
INSERT INTO vacation  VALUES (DATE()+6,DATE()+7)
INSERT INTO vacation  VALUES (DATE()+8,DATE()+8)
INSERT INTO vacation  VALUES (DATE()+10,DATE()+15)
INSERT INTO vacation  VALUES (DATE()+17,DATE()+20)

INDEX ON dstart FOR NOT DELETED() TAG T1

clear
FOR D = 0 TO 25

dbefore=DATE()+d
? dbefore

SELECT NVL(MAX(T1.dend)+1,date(1960,1,1)) dFreeMaxFirst ;
	FROM vacation T1 ;
	WHERE T1.dend < m.dbefore;
		AND NOT exists(SELECT * FROM vacation T2 WHERE t2.dstart - 1 = T1.dend );
		 INTO CURSOR result
?? 	 dFreeMaxFirst  at 20
* IF dstart is not nullable this is better
SELECT NVL(MAX(T1.dend)+1,date(1960,1,1)) dFreeMaxFirst ;
	FROM vacation T1 ;
	WHERE dend < m.dbefore;
		AND dend + 1 NOT IN (SELECT dstart FROM vacation);
		 INTO CURSOR result

 ?? dFreeMaxFirst at 40
NEXT