Nobody agrees on holidays. There are the obvious international differences – the Fourth of July doesn’t have the same appeal to the UK that it does to the US, and most Americans have no idea what Bank holidays<\/a> are, and why the banks deserve them. This can be a real problem in certain industries.\u00a0The ocean freight and maritime industries publish a list of all the non-working holidays for every seaport on Earth. Air freight is not so bothered by this, but strong dock worker unions mean that if you show up in a port on the wrong day, you can pay extra docking fees.\u00a0<\/p>\n Holidays fall into two types. Some can be computed from a rule and some are simply declared by an authority. The declared holidays are usually civic ones and they might change.\u00a0 The computed ones that involve lunar and\/or solar cycles are mostly religious. You also have holidays that appear only once, such as a “National Day of Mourning” for the death of an important political figure.\u00a0<\/p>\n But even the familiar holidays are harder to compute than you think.\u00a0 Every programmer is aware that Easter has a pretty elaborate formula<\/a>. But nobody asks “Which Easter?” we are to compute.\u00a0 Orthodox and Catholic (which is used by Protestants) dates do not agree.\u00a0 For example, in 2008 the Catholic Easter was on March 10 and the Greek Orthodox Easter on April 14; but by 2010 they will both be on March 22.\u00a0 You can get a side by side comparison at Side-by-side Easter calendar reference for the 21st century<\/a>.\u00a0 <\/em><\/p>\n Some holidays are longer than others. The obvious example is the Weekends.\u00a0 That usually means Saturdays and Sundays in Western countries.\u00a0 In the Islamic world, it means Fridays.\u00a0 And probably in my lifetime, we will probably see the four day work week in some countries.\u00a0 Then there is Hanukkah<\/a> and Eid ul-Fitr<\/a>, which last for several days.\u00a0<\/p>\n In short, holidays are just too complicated and arbitrary to put in a function, VIEW or a query.\u00a0 I wish that there was even an ISO Standard for just classifying types of holidays.\u00a0 What this means is that you cannot help but use a Calendar table for holidays and you have to do most of the work for yourself.\u00a0 We will get to that in another section.\u00a0<\/p>\n Be grateful that most commercial work is done at the level of days and perhaps time of day, and not to the accuracy of decimal fractions of seconds. That leads to the choice of atomic versus the astronomical clocks, if you have to co-ordinate events at the nanosecond level.\u00a0 Scientific work has to cope with leap-seconds.\u00a0 These are positive or negative corrections defined by the International Earth Rotation and Reference System Service to keep the atomic clocks within 0.9 seconds of the planet’s actual rotation.\u00a0 They announced on 2008 July 4 that the world’s atomic clocks will be adding another second at the end of this year.\u00a0 I hope this does not spoil your New Years Eve plans.\u00a0<\/p>\n The simplest Calendar is just a single column with the dates for a particular range.\u00a0 Since it is all key, we know that It is as normalized as it can get.\u00a0 Since there are 365.2422 days per year, a 100 year calendar is pretty small and would fit into main storage in most modern machines.\u00a0 It is also of pretty limited use without other data that we want to look up.\u00a0<\/p>\n Since we already talked about holidays, the first impulse is to add a second column with a flag for “work day” or “holiday” to the table, something like this skeleton.\u00a0<\/p>\n The most common calculations you need are the number of business days between two dates or the nearest business day to a given single date.\u00a0 You can do this with the integer flag and earlier SQLs with a skeleton like this:<\/p>\n This interferes with the ability to get other data out of Foobar without adding This is still not as good as we can make it.\u00a0 Let me take time out to discuss the Julian date, which is often misused.\u00a0 Julian date is technically a count of days used by astronomers for observations (http:\/\/en.wikipedia.org\/wiki\/Julian_date).\u00a0 It is a large integer number and has some rules for computing it.\u00a0 Only astronomers care about it, and the rest of us get it confused with a Julian-IZED date,<\/p>\n The Julianized date is a count of days from a base day, used in business.\u00a0 You see it as the “day within year” number on the bottom of the pages of a desk calendar.\u00a0 If your time frame is a calendar year, it will be between 1 and 365 or 366 days.\u00a0 It is called the ordinal day format (“yyyy-ddd”) in ISO-8601.\u00a0 But you can Julianize any time frame you wish such as the entire range covered by your Calendar table.\u00a0 Instead of a flag, you simply repeat the Julianized number for holidays and use integer math to get the counts.\u00a0 Here is a skeleton.\u00a0<\/p>\n To compute the business days from ‘2007-04-05’, Thursday before Easter in 2007 to ‘2007-04-10’, the next Tuesday:<\/p>\n This saves having to scan all of the rows within the range to get a sum.\u00a0 The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company. \u00a0<\/p>\n A common application is have special reporting periods, perhaps based on the fiscal calendar or business defined events (“Annual Going out Of Business Sale!” was one of my favorites).\u00a0<\/p>\n These reports can overlap; a fiscal quarter will be contained in the range of its fiscal year.\u00a0 There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales.\u00a0 But we want to know their ranges so that the table is fairly constant once it is created.\u00a0<\/p>\n The basic skeleton for use with these tables is<\/p>\n The <<report name list>> would probably be events nested inside each other, like fiscal quarters inside a fiscal year.\u00a0 While this is a useful and portable programming trick, you need to consider replacing it with the newer OLAP extensions to the Another form of calendar table records the history of events.\u00a0 You will see history tables used in Data Warehouses and queries dealing with things that have duration. If you go to the French Quarter in New Orleans, there is a lamp post with a historical plaque on it which gives the years of the Spanish domination, French domination, American domination, Confederate domination and finally, the second American domination.\u00a0 The last entry has a blank space to be filled in later and empty room on the plaque beneath that line.\u00a0<\/p>\n Now look at the To make this easier to see, let me use a history table for price changes.\u00a0 So a basic history table looks like this skeleton:<\/p>\n The first three uniqueness constraints are fairly obvious.\u00a0 The The next constraints give an ordering to each event, namely, The self-referencing constraint is a trick from Leap Seconds and Trivia\u00a0\u00a0<\/h2>\n
Calendar Tables <\/b><\/h2>\n
CREATE TABLE Calendar\r\n(cal_date DATE NOT NULL PRIMARY KEY,\r\n\u00a0business_day_flg INTEGER DEFAULT 1 NOT NULL\r\n\u00a0 CHECK (business_day_flg IN (0,1)), \r\n\u00a0...); \r\n<\/pre>\n
SELECT SUM(C.business_day_flg) AS business_day_cnt\r\n\u00a0 FROM Calendar AS C, Foobar AS F\r\n\u00a0WHERE C.cal_date BETWEEN F.foo_start_date AND F.foo_end_date\r\n\u00a0\u00a0 AND ..;\r\n<\/pre>\n
GROUP BY<\/code> clauses.\u00a0 Today you can use the windowed aggregates functions – that is the new ones with an OVER() <\/code>clause.\u00a0<\/p>\nSELECT F.*, -- use real column names in production code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(C.business_day_flg) \r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0OVER (ORDER BY C.cal_date) AS business_day_cnt\r\n\u00a0 FROM Calendar AS C, Foobar AS F\r\n\u00a0WHERE C.cal_date BETWEEN F.foo_start_date AND F.foo_end_date\r\n\u00a0\u00a0 AND ..;\r\n<\/pre>\n
CREATE TABLE Calendar\r\n(cal_date DATE NOT NULL PRIMARY KEY,\r\n\u00a0julian_business_nbr INTEGER NOT NULL,\r\n\u00a0...);\r\n\u00a0\r\nINSERT INTO Calendar VALUES ('2007-04-05', 42);\r\nINSERT INTO Calendar VALUES ('2007-04-06', 43); -- Good Friday\r\nINSERT INTO Calendar VALUES ('2007-04-07', 43);\r\nINSERT INTO Calendar VALUES ('2007-04-08', 43); -- Easter Sunday\r\nINSERT INTO Calendar VALUES ('2007-04-09', 44);\r\nINSERT INTO Calendar VALUES ('2007-04-10', 45); -- Tuesday, back to work \r\n<\/pre>\nSELECT (C2.julian_business_nbr - C1.julian_business_nbr -1) AS business_day_cnt \r\n\u00a0 FROM Calendar AS C1, Calendar AS C2\r\n\u00a0WHERE C1.cal_date = '2007-04-05'\r\n\u00a0\u00a0 AND C2.cal_date = '2007-04-10';\r\n<\/pre>\n
Report Range Tables <\/b><\/h2>\n
CREATE TABLE ReportRanges\r\n(report_name VARCHAR(30) NOT NULL PRIMARY KEY,\r\n\u00a0report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, \r\n\u00a0report_end_date DATE NOT NULL,\r\n\u00a0CONSTRAINT date_ordering \r\n\u00a0 CHECK (report_start_date <= report_end_date), \r\n\u00a0etc);\r\n<\/pre>\n
SELECT R.report_name, << summary computations >> \r\n\u00a0 FROM ReportRanges AS R, [Events] AS E\r\n\u00a0WHERE E.event_date BETWEEN R.report_start_date AND report_end_date \r\n\u00a0\u00a0 AND R.report_name IN (<<report name list>>)\r\n\u00a0GROUP BY R.report_name; \r\n<\/pre>\n
GROUP BY <\/code>clause such as ROLLUP<\/code> and CUBE.\u00a0<\/code><\/p>\nHistory Tables <\/b><\/h2>\n
NULL<\/code>-able end_date<\/b> in the skeleton that follows.\u00a0 It is useful because SQL does not have a token for “Eternity” to show that something is not yet finished like New Orleans.\u00a0 Using a dummy date in the distant future for “Eternity” has portability problems.\u00a0 And it can mess up temporal math if you don’t catch it in CASE<\/code> expressions.\u00a0 There should be, at most, one NULL<\/code> for each item in this inventory history to model the current price.\u00a0 It is also possible that we know a future ending data for an item – look at the expiration dates on food.\u00a0\u00a0<\/p>\nCREATE TABLE PriceHistory\r\n(ean CHAR(13) NOT NULL\u00a0\u00a0 -- industry standard barcode \r\n\u00a0 REFERENCES Inventory(ean),\r\n\u00a0price_prev_date DATE NOT NULL, \r\n\u00a0price_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, \r\n\u00a0price_end_date DATE, -- NULL means current price \r\n\u00a0item_price DECIMAL (12,4) NOT NULL\r\n\u00a0 CHECK (item_price > 0.0000), \r\n\u00a0etc.\r\n\u00a0 -- now we add constraints \r\n\u00a0\r\n--uniqueness \r\nPRIMARY KEY (ean, price_start_date),\r\nUNIQUE (ean, price_end_date), \r\nUNIQUE (ean, prev_end_date),\r\n\u00a0\r\n--ordering of events \r\nCHECK (price_start_date <= price_end_date),\r\n-- Optionally, CHECK (price_start_date < price_end_date\r\nCHECK (prev_end_date <= price_start_date),\r\n-- Optionally, CHECK (price_start_date = price_prev_date + INTERVAL '1' DAY), -- prevents gaps\r\n\u00a0\r\n-- self-referencing constraint \r\nCONSTRAINT ean_prev_end_date\r\n\u00a0FOREIGN KEY (ean, prev_end_date)\r\n\u00a0REFERENCES PriceHistory (ean, price_end_date)\r\n\u00a0);\r\n<\/pre>\n
EAN<\/code> and the start of a price have to be unique and not NULL<\/code>-able so we have a natural primary key.\u00a0 The ending dates, current and previous, might have NULL<\/code>s, so we need to use a UNIQUE<\/code> constraint.\u00a0<\/p>\nprice_prev_date<\/code> is on or before price_start_date<\/code><\/b>, which is on or before price_end_date<\/code><\/b>.\u00a0 The reason for not putting this into a single BETWEEN<\/code> predicate is that each constraint will have a name in production code that will show up in error messages so we want to be exact.\u00a0<\/p>\n