SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with business days, calendar tables, and test-driven development


Fun with business days, calendar tables, and test-driven development

Author
Message
Alexander Kuznetsov
Alexander Kuznetsov
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 824
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>


That's right, I concur. Thanks!
Alexander Kuznetsov
Alexander Kuznetsov
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 824
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.


If we only have one typical query, then adding one column makes sense. If, however, we have dozens fo different queries, such as "last business day of current month", "first business day of next month" and such, keeping too many columns, and maintaining them if there is an additional holiday (such as President Ford's funeral) becomes very messy.
Alexander Kuznetsov
Alexander Kuznetsov
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 824
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



Iulian,

We have multiple calendars, for countries and individul companies as well. For instance, Chicago Mercantile Exchange's calendar is sometimes different from the offcial US one. All we need to do is to add CalendarName column to the primary key, and @CalendarName parameter to all these functions.
Alvin Ramard
Alvin Ramard
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14197 Visits: 11702
I frequently used a similar table where I used to work. It was the Date Dimension table from our data warehouse. Look around, you may already have something like this, and the logic to build it, on one of your servers.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
Andrew Notarian
Andrew Notarian
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 429
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.


Yup, that's basically the T-SQL I ended up writing. If you want to be picky, there is also a US law that gives federal employees a holiday for presidential inaugurations but only in certain jurisdictions near Washington DC. There are also unplanned holidays like when Gerald Ford dies.
feakesj
feakesj
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 17
Any item of information that can be derived from a date could be stored in the calendar table if it simplifies or improves the performance of queries or code.

Whether or not the data items you mention should be added to the calendar table is an implementation decision, but they all seem plausible candidates.

It is quite normal for a calendar table to have more than 20 data items, e.g. http://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/
JJ B
JJ B
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1643 Visits: 2861
wildh: thanks for looking those links up. One of those was very helpful to me! Why re-invent the wheel.
larry-610474
larry-610474
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 44
A few suggestions:

1) Use the BIT datatype for Is* columns, which eliminates the need for a CONSTRAINT

2) Make the primary key of the calendar an INT in YYYYMMDD format (per Ralph Kimball), which makes joining faster. If you need a DATE representation, just add a column for it

3) If you need the closest business date on or before the current date, just add a column for it and populate it once when you create the table. Along the same lines, you may add columns for DayNumberOfYear, WeekNumberOfYear, MonthNumberOfYear, NumberOfHolidaysThisMonth, NumberOfHolidaysThisYear, etc.

Larry.Shiller@gmail.com
Alexander Kuznetsov
Alexander Kuznetsov
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 824
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';


Yes, we do it too. When a new holiday comes up, like President Ford's funeral, we update all those future numbers.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search