Re: How to find first non-vacation day - Mailing list pgsql-general

> 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