John Mitchell-245523 (12/28/2012)

---

CELKO (12/28/2012)

---

Here is atrick weith a calendar table.

CREATE TABLE Calendar

(cal_date DATE NOT NULL PRIMARY KEY,

julian_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

To compute the business days from Thursday of this week to next

Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)

FROM Calendar AS C1, Calendar AS C2

WHERE C1.cal_date = '2007-04-05',

AND C2.cal_date = '2007-04-10';

That's got to be a maintenance nightmare, hasn't it? Every time you want to add a new holiday, you have to change the julian date for all subsequent rows. And is that join ANSI compliant?

How about this?

CREATE TABLE Calendar

(cal_date DATE NOT NULL PRIMARY KEY,

working_day bit NOT NULL,

);

INSERT INTO Calendar

VALUES ('2007-04-05', 1),

('2007-04-06', 0), -- good Friday

('2007-04-07', 1),

('2007-04-08', 0), -- Easter Sunday

('2007-04-09', 1),

('2007-04-10', 1); --Tuesday

SELECT SUM(CAST(working_day AS int))

FROM Calendar

WHERE cal_date >= '2007-04-05'

AND cal_date < '2007-04-10';

John