Thread: How to find first non-vacation day
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 ?
Andrus.
> 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
>> 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.
"Andrus Moor" <eetasoft@online.ee> 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 ?
People try to get baroquely clever about building tiny tables to
represent these things; it tends not to work out well, because the
queries get even more baroque...
I'd create a table of all the days of the year:
create table days (
a_day date,
primary key(a_day)
);
Fill it in with 365 values:
insert into days
select '2005-12-31'::date + (generate_series||'days')::interval from generate_series(1,365);
Suppose vacations are thus...
/* cbbrowne@[local]/dba2 ~=*/ select * from vacation;
dstart | dend
------------+------------
2006-01-01 | 2006-01-01
2006-03-15 | 2006-03-19
2006-12-24 | 2006-12-25
(3 rows)
Forget about your representation of vacation; replace it with the
following "set of vacation days":
create table vacation_days as select distinct a_day from vacation, days where a_day between dstart and dend;
Now, to find the *last working day* before, oh, say, 2006-03-18...
/* cbbrowne@[local]/dba2 ~=*/ select max(a_day) from (select a_day from days where a_day not in (select a_day from
vacation_days))as non_vac_days where a_day < '2006-03-18';
max
------------
2006-03-14
(1 row)
Determining cost...
/* cbbrowne@[local]/dba2 ~=*/ explain analyze select max(a_day) from (select a_day from days where a_day not in (select
a_dayfrom vacation_days)) as non_vac_days where a_day < '2006-03-18';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5.43..5.44 rows=1 width=4) (actual time=0.644..0.647 rows=1 loops=1)
-> Index Scan using days_pkey on days (cost=1.10..5.33 rows=38 width=4) (actual time=0.112..0.406 rows=72 loops=1)
Index Cond: (a_day < '2006-03-18'::date)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on vacation_days (cost=0.00..1.08 rows=8 width=4) (actual time=0.006..0.033 rows=8 loops=1)
Total runtime: 0.729 ms
(7 rows)
If you're doing a lot of calculations of "work days," then it would
make a lot of sense to create a "materialized calendar" representing
the work days of the year...
--- Start with all days
create table work_calendar as select a_day from days;
create unique index wc_day on work_calendar (a_day);
--- Drop out Saturday/Sunday
delete from work_calendar where date_part('dow', a_day) not in (0,6);
--- Drop out vacation days
delete from work_calendar where a_day in (select a_day from days, vacation where a_day between dstart and dend);
/* cbbrowne@[local]/dba2 ~=*/ select max(a_day) from work_calendar where a_day < '2006-03-18';
max
------------
2006-03-14
(1 row)
/* cbbrowne@[local]/dba2 ~=*/ explain analyze select max(a_day) from work_calendar where a_day < '2006-03-18';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.07..0.08 rows=1 width=0) (actual time=0.043..0.047 rows=1 loops=1)
InitPlan
-> Limit (cost=0.00..0.07 rows=1 width=4) (actual time=0.027..0.030 rows=1 loops=1)
-> Index Scan Backward using wc_day on work_calendar (cost=0.00..3.73 rows=54 width=4) (actual
time=0.019..0.019rows=1 loops=1)
Index Cond: (a_day < '2006-03-18'::date)
Filter: (a_day IS NOT NULL)
Total runtime: 0.101 ms
(7 rows)
The overall point: If you create the calendars as sets of days, then
SQL provides you with *excellent* ways of manipulating them as sets
where you say things like "where day is in this set" and "where day is
*not* in that set" and such.
--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/nonrdbms.html
"If the programmer can simulate a construct faster then the compiler
can implement the construct itself, then the compiler writer has blown
it badly." -- Guy L. Steele, Jr., Tartan Laboratories
postgresql@philip.pjkh.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)