Alexander Kuznetsov (9/22/2010)

---

Comments posted to this topic are about the item <A HREF="/articles/Test-Driven+Development/71075/">Fun with business days, calendar tables, and test-driven development</A>

feakesj (9/23/2010)

---

The article tries to cover too much ground and ends up doing none of the topics justice. In particular, the first function described would have been much better implemented as an additional column on the calendar table -- a key benefit of such tables is that they allow results to be looked up rather than calculated.

Iulian -207023 (9/23/2010)

---

Very nice article Alex, thank you.

Indeed IsBusinessDate 'Y' or 'N' is great for building an understanding of the concepts, I remember when I implemented this functionality using something similar; I used the name of the holiday in the HolidayName field ('Sunday', 'Saturday', 'Easter', 'Independence day', etc. ) and considering as business day the records where HolidayName is null or empty.
This helped me when I was asked "why there are less working days this month?" , and I had the answer at hand. Also it was easy for the HR to define company's holidays as they might change during the year, by just typing the name of the holiday in the interface.

I am curious in approaching multiple calendars, i.e. for different departments or for different geographies.

For example: National days are different for most of the countries but also some religious holidays, Italian branch of one company has the Easter holiday in another period of time than Russian branch.

Also for production department some Saturdays might be business/working days since for sales dept. Saturdays might be weekends/non business days.

Kind regards,
Iulian Cozma

wildh (9/23/2010)

---

Is this any good - http://facility9.com/2009/04/23/populating-us-federal-holidays-in-a-calendar-table/
or -
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

We have a similar issue in the UK but it's only a 10 min job to crib it from a web site into a update script. But hang on, then it's a 4 month wait for the DBA's to run it.

CELKO (9/23/2010)

---

I still prefer Julianizing the business days to make the math easier

CREATE TABLE Calendar
(cal_date DATE NOT NUL 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
Tuesday:

SELECT (C2. julian_business_nbr - C1. julian_business_nbr)
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05',
AND C2cal_date = '2007-04-10';