palewire

If you’d ever like to programmatically determine the number of federal working days between two dates, here’s a quick and dirty solution I cooked up the other day in Microsoft SQL Server. It can be useful in monitering deadlines and evaluating bureaucratic processing speeds (say, for example, a log of Freedom of Information Act requests).

It’s a three-step process.

1. Create a calendar table that distinguishes working days from weekends and holidays.
2. Create a user-defined function that will use that calendar to count the number of working days between two dates.
3. Count the days.

Below you can find a calendar creation script I adapted from one published on aspfaq.com. Besides distinguishing weekdays from weekends, it marks off all federal holidays — as specificed by the U.S. Office of Personnel Management — from Jan. 1, 2000 to Dec. 31, 2010.

First we create the calendar table. It will have three fields, one for the date and then two binary fields, one that will automatically determine the weekdays using the DATEPART function, and another that we will use later to designate working days.

CREATE TABLE dbo.FederalCalendar (
dt SMALLDATETIME PRIMARY KEY CLUSTERED,
isWeekDay AS CONVERT(BIT, CASE
WHEN DATEPART(dw, dt) IN (1,7) THEN 0
ELSE 1 END),
isWorkDay BIT DEFAULT 1
);
GO

Next we need to populate the date field, dt, with the range of days in our calendar. In this case, it will be from Jan. 1, 2000 through Dec. 31, 2010.

DECLARE @dt SMALLDATETIME;
SET @dt = '20000101';
WHILE @dt<= '20101231'
BEGIN
INSERT dbo.FederalCalendar(dt) SELECT @dt;
SET @dt = @dt + 1;
END

Then winnow down our working days field by eliminating the weekends.

UPDATE dbo.FederalCalendar
SET isWorkDay = 0
WHERE isWeekday = 0;

And finish the job by knocking out all of the federal holidays. Of course you could modify this to reflect any other schedule you’d like to work with, such as trading days on Wall Street or the official holidays in your state.

UPDATE dbo.FederalCalendar
SET isWorkDay = 0
WHERE isWorkDay = 1
AND dt IN
(
--2000 Federal Holidays
-- New Year's Day holiday was observed on 12/31/1999
'20000117', -- Martin Luther King's Birthday
'20000221', -- George Washington's Birthday
'20000529', -- Memorial Day
'20000704', -- Independence Day
'20000904', -- Labor Day
'20001009', -- Columbus Day
'20001110', -- Veterans Day
'20001123', -- Thanksgiving Day
'20001225', -- Christmas Day

--2001 Federal Holidays
'20010101', -- New Year's Day
'20010115', -- Martin Luther King's Birthday
'20010219', -- George Washington's Birthday
'20010528', -- Memorial Day
'20010704', -- Independence Day
'20010903', -- Labor Day
'20011008', -- Columbus Day
'20011112', -- Veterans Day
'20011122', -- Thanksgiving Day
'20011225', -- Christmas Day

--2002 Federal Holidays
'20020101', -- New Year's Day
'20020117', -- Martin Luther King's Birthday
'20020218', -- George Washington's Birthday
'20020527', -- Memorial Day
'20020704', -- Independence Day
'20020902', -- Labor Day
'20021014', -- Columbus Day
'20021111', -- Veterans Day
'20021128', -- Thanksgiving Day
'20021225', -- Christmas Day

--2003 Federal Holidays
'20030101', -- New Year's Day
'20030120', -- Martin Luther King's Birthday
'20030217', -- George Washington's Birthday
'20030526', -- Memorial Day
'20030704', -- Independence Day
'20030901', -- Labor Day
'20031013', -- Columbus Day
'20031111', -- Veterans Day
'20031127', -- Thanksgiving Day
'20031225', -- Christmas Day

--2004 Federal Holidays
'20040101', -- New Year's Day
'20040119', -- Martin Luther King's Birthday
'20040216', -- George Washington's Birthday
'20040531', -- Memorial Day
'20040705', -- Independence Day
'20040906', -- Labor Day
'20041011', -- Columbus Day
'20041111', -- Veterans Day
'20041125', -- Thanksgiving Day
'20041224', -- Christmas Day

--2005 Federal Holidays
'20041231', -- New Year's Day
'20050117', -- Martin Luther King's Birthday
'20050221', -- George Washington's Birthday
'20050530', -- Memorial Day
'20050704', -- Independence Day
'20050905', -- Labor Day
'20051010', -- Columbus Day
'20051111', -- Veterans Day
'20051124', -- Thanksgiving Day
'20051226', -- Christmas Day

--2006 Federal Holidays
'20060102', -- New Year's Day
'20060116', -- Martin Luther King's Birthday
'20060220', -- George Washington's Birthday
'20060529', -- Memorial Day
'20060704', -- Independence Day
'20060904', -- Labor Day
'20061009', -- Columbus Day
'20061110', -- Veterans Day
'20061123', -- Thanksgiving Day
'20061225', -- Christmas Day

--2007 Federal Holidays
'20070101', -- New Years Day
'20070115', -- Martin Luther King's Birthday
'20070219', -- George Washington's Birthday
'20070528', -- Memorial Day
'20070704', -- Independence Day
'20070903', -- Labor Day
'20071008', -- Columbus Day
'20071112', -- Veterans Day
'20071122', -- Thanksgiving Day
'20071225', -- Christmas Day

--2008 Federal Holidays
'20080101', -- New Years Day
'20080121', -- Martin Luther King's Birthday
'20080218', -- George Washington's Birthday
'20080526', -- Memorial Day
'20080704', -- Independence Day
'20080901', -- Labor Day
'20081013', -- Columbus Day
'20081111', -- Veterans Day
'20081127', -- Thanksgiving Day
'20081225', -- Christmas Day

--2009 Federal Holidays
'20090101', -- New Years Day
'20090119', -- Martin Luther King's Birthday
'20090216', -- George Washington's Birthday
'20090525', -- Memorial Day
'20090703', -- Independence Day
'20090907', -- Labor Day
'20091012', -- Columbus Day
'20091111', -- Veterans Day
'20091126', -- Thanksgiving Day
'20091225', -- Christmas Day

--2010 Federal Holidays
'20100101', -- New Years Day
'20100118', -- Martin Luther King's Birthday
'20100215', -- George Washington's Birthday
'20100531', -- Memorial Day
'20100705', -- Independence Day
'20100906', -- Labor Day
'20101011', -- Columbus Day
'20101111', -- Veterans Day
'20101125', -- Thanksgiving Day
'20101225' -- Christmas Day
);

Now that the calendar’s done, here’s a script that will create a user-defined function to take two dates and count the number of federal working days between the them.

CREATE FUNCTION [dbo].[FederalWorkingDays]
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @result INT

SELECT @result = COUNT(*)
FROM dbo.FederalCalendar
WHERE dt >= @startDate
AND dt <= @endDate
AND isWorkDay = 1;

RETURN @result

END

Once you’ve run that, all you should need to do to is write a query that uses your new function. Here are two examples:

SELECT dbo.FederalWorkingDays(datefield1, datefield2)
FROM table

SELECT dbo.FederalWorkingDays('01/01/2007', '02/14/2007')

And that’s the end of our journey. I hope it can be helpful to somebody. As always, if there’s something screwed up or missed, just drop a comment. We’ll sort things out.

About two months ago I started using the social bookmarking site del.icio.us to save and tag my favorite news stories. A couple hundreds links later, I’ve built a nice collection. Below you can find the tags I’ve selected displayed visually.

If you’ve never seen one, this is what is known as a tag cloud. The crowd over at Wikipedia defines it this way:

A Tag Cloud is a text-based depiction of tags across a body of content to show frequency of tag usage and enable topic browsing. In general, the more commonly used tags are displayed with a larger font or stronger emphasis. Each term in the tag cloud is a link to the collection of items that have that tag.

While it makes for a fun little toy, the whole effort is certainly hampered by capricious and inconsistent coding on my part. At its core, this project is founded on mapping complex news stories to simple nominal categories for quantitative analysis. Because the creation and execution of my coding routines have been, to be kind, pretty loose, you shouldn’t expect more than a foggy view on the perculiarities of how I consume and categorize news. For insight into the news itself, it’s best you trust the professionals.

Should anyone be interested, you can track a feed of my latest links in the left side bar under the heading Ben’s News Bag — also available via RSS — and keep up with the news cloud here or on my media diet page, where I’ve installed an identical module just above my blogroll.

Here’s the story:

[President George W. Bush] has entered a book-reading competition with Karl Rove, his political adviser. White House aides say the president has read 60 books so far this year (while the brainy Rove, to Bush’s competitive delight, has racked up only 50).

— Walsh, Kenneth. “A Humbled Presidency,” U.S. News & World Report. August 20, 2006. Hyperlink.

So how much reading is that, really? It doesn’t sound too “humble” to me.

My brief survey of media articles on the subject turned up only 31 titles, published below.

Title	Author(s)	Pages	Source
After Fidel: The Inside Story of Castro’s Regime and Cuba’s Next Leader	Latell, Brian	288	CSPAN
American Prometheus: The Triumph and Tragedy of J. Robert Oppenheimer	Bird, Kai & Sherwin, Martin J.	736	CSPAN
Beach Road	Patterson, James & de Jonge, Peter	400	CSPAN
Big Bam, The: The Life and Times of Babe Ruth	Montville, Leigh	400	CSPAN
Bridge at Andau, The	Michener, James	288	CSPAN
Challenger Park	Harrigan, Stephen	416	CSPAN
Cinnamon Skin: Travis McGee Mysteries	MacDonald, John D.	336	CSPAN
Clemente: The Passion and Grace of Baseball’s Last Hero	Maraniss, David	416	CSPAN
Decision at Sea: Five Naval Battles that Shaped American History	Symonds, Craig	400	CSPAN
Dreadful Lemon Sky, The	MacDonald, John D.	320	CSPAN
Finding Fish: A Memoir	Quenton Fisher, Antwone	352	CSPAN
Flashman at the Charge	MacDonald Fraser, George	336	CSPAN
Flash for Freedom	MacDonald Fraser, George	352	CSPAN
Hamlet	Shakespeare, William	208	CSPAN
King Leopold’s Ghost	Hochschild, Adam	384	Alternet
Lincoln: A Life of Purpose and Power	Carwardine, Richard	416	CSPAN
Lincoln’s Greatest Speech: The Second Inaugural	White Jr., Ronald C.	256	CSPAN
Macbeth	Shakespeare, William	256	CSPAN
Manhunt: The 12-Day Chase for Lincoln’s Killer	Swanson, James L.	464	CSPAN
Mao: The Unknown Story	Chang, Jung & Halliday, Jon	832	US News
Mayflower: A Story of Courage, Community and War	Philbrick, Nathanial	480	CSPAN
Messenger, The	Silva, Daniel	352	CSPAN
Nine Parts of Desire: The Hidden World of Islamic Women	Brooks, Geraldine	255	US News
Places in Between, The	Stewert, Rory	320	CSPAN
Polio: An American Story	Oshinsky, David	368	CSPAN
Promised Land, Crusader State: The American Encounter with the World Since 1776	McDougall, Walter	304	CSPAN
Quick Red Fox	MacDonald, John D.	320	CSPAN
Revolutionary Characters: What Made the Founders Different	Wood, Gordon S.	336	CSPAN
A Savage War of Peace	Horne, Alistair	624	NYTimes
Stranger, The	Camus, Albert	160	CSPAN
Through a Glass Darkly: A Commissario Guido Brunetti Myster	Leon, Donna	272	CSPAN

According to listings at Amazon.com, the total page count is 11,647 pages. If you divide that number by 365 you get 31.9, which is how many pages someone would have to read every single day of the year to finish all 31 books.

Impressive enough, right? Does anyone here read more than 30 pages a day?

Yet, remember, the claim isn’t that President Bush read 31 books last year. No, the claim is that he read 60 books—and all of them by August 20.

Without knowing the complete list of titles, it’s impossible to generate an exact page total. One alternative is to assume that the rest of the books were of approximately the same length as the titles we do know. The average length of the 31 books listed above is just over 375 pages. Since we’re 29 books short of our total, let’s multiply that by 375, which rounds out to about 10,895 additional pages of reading. That nearly doubles our earlier total of 11,647 up to an estimated 22,543 pages of reading.

A quick run through the calendar shows August 20 to have been the 232nd day of the year. What’s 22,543 divided by 232? It’s 97. So, according to our calculations, President Bush would have to had to read approximately 97 pages every day for 232 consecutive days to have mowed through a sixty-book reading list.

Without getting into the academic research on reading rates and comprehension, let’s just assume that the President proceeded at the straightforward and brisk pace of one page per minute. The simple 1:1 page-to-minute ratio demands more than an hour and a half of reading time each and every day. Two minutes per page? That, of course, doubles the president’s daily reading regime to more than three hours every day.

Now, far be it from me to judge anyone here, but assuming my math is right that’s a pretty steep hill for anyone to climb. My guess is that it’s likely to have been a wee bit of an exaggeration.

For anyone interested, here are the presidential book titles I found from before 2006.

Title	Author(s)	Pages	Source
Unnamed Devotional	Chambers, Oswald	?	NYTimes
Alexander Hamilton	Chernow, Ronald C.	832	NYTimes
Alexander II: The Last Great Tsar	Radzinsky, Edvard	480	The Book Blog
April 1865: The Month That Saved America	Winik, Jay	480	CNN
Case for Democracy, The: The Power of Freedom to Overcome Tyranny and Terror	Sharansky, Natan	303	CNN
Great Influenza, The: The Epic Story of the Deadliest Plague in History	Barry, John	560	The Book Blog
His Excellency: George Washington	Ellis, Joseph J.	352	NYTimes
Bible, The (Standard NRSV copy)		?	NYTimes
I Am Charlotte Simmons	Wolfe, Tom	688	NYTimes
Imperial Grunts: The American Military on the Ground	Kaplan, Robert	448	Znet
Salt: A World History	Kurlansky, Mark	496	The Book Blog
Supreme Command	Cohen, Eliot A.	288	WashingtonPost
When Trumpets Calls: Theodore Roosevelt After the White House	O’Toole, Patricia	512	Znet