-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Easiest thing to do is create a table of dates for business days &
holidays. Then use that table to discover the appropriate dates.
CREATE TABLE Calendar (
begin_date DATETIME NOT NULL ,
end_date DATETIME NOT NULL ,
date_type CHAR(1) NOT NULL
CHECK date_type IN ("B", "H"),
CONSTRAINT PK_Calendar PRIMARY KEY (begin_date, end_date, date_type),
CHECK (begin_date < end_date)
)
date_types: B - Business day(s), H - Holiday(s).
Query to get rows (records) from your table (table_name) that have a
date 3 biz days ago:
PARAMETERS [What Date?] Date;
SELECT T.date_column, < other columns >
FROM table_name AS T INNER JOIN Calendar As C
ON T.date_column BETWEEN C.begin_date AND C.end_date
WHERE C.end_date =
(SELECT MAX(end_date) FROM Calendar
WHERE end_date <= [What Date?] - 3
AND date_type <> "H")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQ+uxaoechKqOuFEgEQIbEACgr+WZ4OUz4o+etSAwHNGZyFidoCAAn2LF
4QeuoA1moJDcoF01hlKLTdTH
=nrXe
-----END PGP SIGNATURE-----
I am looking for assistance creating a query on a date field that will go
back three business days from the current date. It must not show inclusive
days, as in yesterday and the day before. Just the 3rd day past and not
counting weekends. Also holidays would need to be considered.
Thanks for any assistance
RW
