Re: How to find first non-vacation day

postgresql(at)philip(dot)pjkh(dot)com (Philip Hallstrom) writes:

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

The "big win" comes if you realize that "vacation," "the whole year,"
"work days," and such are all nicely described as "sets," and that SQL
is fairly excellent at representing set operations.

So create a calendar table that is the set of days in the year.

Create a vacation table that is the set of vacation days in the year.

That, combined with indication of other scheduled "non-working days"
such as weekends, can easily define a set of days that are the "work
calendar."

There will be dozens or hundreds of entries in each table; that's
fine, they'll still be small tables, easily searched for
commonality/difference.
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www3.sympatico.ca/cbbrowne/oses.html
"Options to reboot are: -n Avoids the sync. It can be used if a disk
or the processor is on fire." -- reboot(8)