Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Fun with business days, calendar tables, and test-driven development Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 8:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, 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!
Post #992094
Posted Thursday, September 23, 2010 8:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, 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.
Post #992098
Posted Thursday, September 23, 2010 9:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, 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.
Post #992107
Posted Thursday, September 23, 2010 9:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 3,147, Visits: 7,969

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.
Post #992112
Posted Thursday, September 23, 2010 9:15 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 1, 2014 12:54 PM
Points: 611, Visits: 362
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.
Post #992113
Posted Thursday, September 23, 2010 9:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 19, 2010 3:12 AM
Points: 3, 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/
Post #992124
Posted Thursday, September 23, 2010 11:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 266, Visits: 2,584
wildh: thanks for looking those links up. One of those was very helpful to me! Why re-invent the wheel.
Post #992212
Posted Thursday, September 23, 2010 11:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 1,945, Visits: 2,891
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';


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #992238
Posted Friday, September 24, 2010 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 11, 2012 8:34 PM
Points: 3, 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
Post #992825
Posted Friday, September 24, 2010 2:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, 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.
Post #993098
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse