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.
