Hi All,
I am trying to generate a dynamic date value as part of a much larger query.
The date must be at least one day prior to the ship date and must not be a
weekend or a holiday.
I have created a table to hold the holidays and I am now trying to develop the
query to give me the date. In the code below I have hard coded the order date
and the ship date but in the final query these are already extracted as part
of the larger query.
This is my first time using a series and I am not sure how to connect it to
the holiday table. There may be other mistakes in my query as well. At this
time the error that I am getting is that the dates column soen't exist.
Any hints or pointers to relevant documenation will be appreciated.
CREATE TABLE tbl_holidays
(
holiday date NOT NULL,
CONSTRAINT tbl_holidays_pkey PRIMARY KEY (holiday)
)
WITHOUT OIDS;
INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-24'::date);
INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-25'::date);
INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-26'::date);
INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-30'::date);
SELECT '2005-09-07'::date + s.a AS dates --Generate a years worth of
FROM generate_series(0,365) AS s(a) --possible ship dates starting
--with the date the order was
--placed.
JOIN tbl_holidays --Join to the holiday table to
ON ( dates = tbl_holidays.holiday ) --eliminate holidays from the
--series.
WHERE dates <= ( '2005-09-12'::date - --The date must be at least
interval '1 day' --one day prior to the ship date.
)::date
AND extract( dow FROM dates --The date must be during the
) IN (1, 2, 3, 4, 5) --work week.
Kind Regards,
Keith
