Re: Joining a series of dates

"Keith Worthington" <keithw(at)narrowpathinc(dot)com> writes:
> Here is what I have figured out. I can use two querys to get the data and
> then EXCEPT them together to eliminate the holidays. That part works fine.
> So using 2005-09-07 as an order date and 2005-11-28 as a ship date this is
> what I end up with.

> SELECT max( dates ) AS completion_date
> FROM ( SELECT '2005-09-07'::date + s.a AS dates
> FROM generate_series(0,365) AS s(a)
> EXCEPT
> SELECT holiday
> FROM interface.tbl_holidays
> ) AS valid_dates
> WHERE dates <= ('2005-11-28'::date - interval '1 day')::date
> AND NOT extract(dow FROM dates) IN (0, 6);

SQL is really entirely the wrong tool for this job, or at least you are
applying it in the least efficient possible way. It seems to me that
you want to step backwards from the ship date, discarding weekend dates
(easily checked) and then discarding holidays. Since there are few
holidays this should generally require only one probe into the holidays
table, and not too many dates considered in total. As you've got it
coded above, the entire process is gone through for 365 different dates,
after which you proceed to compare the dates and throw away all but one.

I would personally tend to write this as a plpgsql function containing a
for-loop. Something like

create function prior_working_day(date) returns date as $$
declare d date;
begin
for i in 1..10 loop
d := $1 - i;
if extract(dow from d) not in (0,6) then
if not exists(select 1 from interface.tbl_holidays where hdate = d) then
return d;
end if;
end if;
end loop;
-- if we get here there's something badly wrong
raise exception 'could not find a non-holiday date';
end$$ language plpgsql strict stable;

Just as a finger exercise, we could turn this into a single SQL
operation, but the function is likely to be noticeably faster:

select d from
(select ('2005-11-28'::date - s.a) as d
from generate_series(1,10) as s(a)) ss
where extract(dow from d) not in (0, 6) and
not exists(select 1 from interface.tbl_holidays where hdate = d)
limit 1;

regards, tom lane