tt-615680 - Tuesday, January 30, 2018 8:40 AM
First of all, I suggest you download a copy of "Temporal Queries in SQL" by Rick Snodgrass at the University of Arizona website. It's a free PDF file. Obviously, the natural key for such calendar table is the calendar date and we happen to have a DATE data type. Now get a copy or popularization of the ISO 8601 standards for dates. You want to avoid keeping a local dialect in your calendar table because that's a function of the presentation layer and not the database layer of your tiered architecture.
The Ordinal business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
ordinal_business_nbr INTEGER NOT NULL,
...);
INSERT INTO Calendar
VALUES ('2007-04-05', 42);
('2007-04-06', 43); -- Good Friday
('2007-04-07', 43);
('2007-04-08', 43); -- Easter Sunday
('2007-04-09', 44);
('2007-04-10', 45); -- Tuesday, back to work
To compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:
SELECT (C2.ordinal_business_nbr - C1.ordinal_business_nbr -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';
Please post DDL and follow ANSI/ISO standards when asking for help.