Building a Business Day and Holiday Database Subsystem

• Download source code - 79.4 KB

1. Introduction / Purpose

Many business applications and reports require, as part of their processing, logic flexible and high-performance special aging and date functionality that takes into account weekend dates and holidays of various types. Many existing systems and reports, especially ones built in or on top of Access databases, perform these tasks poorly or slowly if they use iterative cursor processing. There are probably tens of thousands of decent-sized business apps out there that still use this approach, iterating through large quantities of data loaded into old-style ADO or DAO recordsets, via the old "MoveFirst" and "MoveNext" methods. I know, having programmed against VB and Access since their version 1 days a decade and a half ago when I was starting out in IT, I used to do that frequently myself. Fortunately, as it happened, I never had to write reports that processed hundreds of thousands of rows - if I did, I would have been inflicting serious pain on my users unless I learned a better way to do it, and insisted that the application be based on a real database like Oracle or SQL Server. This phase of the project builds the foundation for this kind of system. You will have to wait till phase 3 until you can see how the power is leveraged, but once you take a look at the detail data generated in the table AllDatesDiff, you might have an idea.

2. Database overview

The approach used is data driven, with logic built in to preloaded data files. The database includes the following tables:

• HolidayNames - A base skeleton holiday name table. This table is filled at the time of database creation with one row for each holiday relevant to the business.
• DateFlagsReference - This table is a reference to a flexible integer enumeration of different types of possible holidays, that are stuffed in the database column DateFlags as a bit field. Any particular holiday can belong to one or more categories (types of holidays). For this demonstration system, I included Business and Bank holidays; other types might include Half-Staff or Canadian or other country holidays - it would depend on the particular business requirement what is needed. An alternative design would be to create a boolean column for each type of holiday in the HolidayNames table and the two tables derived from it - this is the purely relational approach and perhaps is cleaner and simpler, but with a price of decreased flexibility. Honestly, if I had time to re-do this mini-design, I might consider this simpler way. Another alternative could be to use a user defined type to define this enumeration.
• HolidayDates - This table contains the base yearly data for each holiday - one row for each holiday per year. The actual dates are derived from a public Web Service running at www.holidaywebservice.com. The Web Service is called from the ASP.NET maintenance application as part of the loading process. At my last job, each year sometime in late December someone had to enter a number of holiday tables and manually modify a script or input dates for the upcoming year. Use of a service like this one solves this problem.
• HolidayDates - A staging table loaded with base holiday date data, to set up the transactional data load via the Stored Procedure DateLoad.
• AllDates - This table once loaded contains 1 day for each day of the year. This design allows easy linkages to the table data without using OUTER joins that return NULL values. NULLs can be troublesome entities and are best avoided unless needed to implement business logic to distinguish 'unknown' from 'empty'.
• AllDatesDiff - This is the aging table containing a series or rows for each day - each row containing a begin date, an end date, and the days difference between the two (in business days). The existence of this table, and views we can derive from it, is the foundation of the utility of this system.
• Registry - A configuration table consisting of various entries in a key-value structure.
• DateLoadLogHistory - A very simple log of successful loads.

3. Database Loading Process

Besides the base tables noted above, this database includes Transact-SQL Stored Procedures that can be executed to populate the tables. The procedures are executed from a .NET data class coded in C#, fronted by an ASP.NET maintenance applet (a WinForms applet could also be used and may be a better choice in some situations). Typically, this process might be run annually some time in December to load next year's data. A business process would have to be set up to document when the procedures are to be run, and access to the process strictly limited to appropriate administrators or IT staffers. Security has not been implemented in the demonstration here, but must be incorporated in any production system.

Below is the primary Stored Procedure that executes the complete data load. Of some interest is the use of the new Try...Catch available in SQL Server 2005. If you are still on SQL Server 2000, remove the Try...Catch and handle errors the old unstructured way. I don't think I am using any other SQL Server 2005 features in the Transact-SQL code so with a few tweaks, you can get it to run.

-- =============================================
-- Description: Runs administrative process to load all the date tables for
-- a calendar year.
-- =============================================

ALTER PROCEDURE [dbo].[DateLoad]
    @LoadYear VARCHAR(4), -- yyyy will work
    @Success CHAR(1) OUT -- returns Y if data loaded successfully, 
                         -- otherwise N

AS
BEGIN
    DECLARE @Found CHAR(1)
    DECLARE @NumRows INT
    DECLARE @StartDt SMALLDATETIME
    DECLARE @EndDt SMALLDATETIME
    DECLARE @StartDtAdj SMALLDATETIME --adjusted to include diff days
    DECLARE @EndDtAdj SMALLDATETIME --adjusted to include diff days
    DECLARE @CalendarDaysMaxDiff SMALLINT
    DECLARE @msg VARCHAR(200)

    BEGIN

        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --makes totally 
        -- sure no other process can modify this data while this txn runs.

        BEGIN TRY

            SET @Success = 'N'
            EXEC dbo.PrintInfoMessage 'Initializing data load.'

            IF LEN(@LoadYear) < 4
            BEGIN
                SET @msg = 
                  'Load year parameter not valid - must be ' +
                  '4 digit year. Value passed: ' + 
                  @LoadYear
                RAISERROR(@msg, 16, 1)
            END

            --check to be sure year was not already loaded
            EXEC DateLoadLogHistoryCheck @LoadYear, @Found = @Found OUT
            IF @Found = 'Y'
            BEGIN
                SET @msg = 'Dates for the year ' + @LoadYear + 
                           ' have already been loaded.'
                RAISERROR(@msg, 16, 1)
            END

            --check to be sure that we have data in the stage
            EXEC dbo.GetHolidayDateStageRowCount @NumRows = @NumRows OUT
            IF @NumRows = 0
            BEGIN
                SET @msg = 'Holiday stage table is not loaded.'
                RAISERROR(@msg, 16, 1)
            END

            -- first, get config setting for days diff
            SET @CalendarDaysMaxDiff = 
                CONVERT(SMALLINT, dbo.RegistryGetItem('maxdaysdiff'))
            IF @CalendarDaysMaxDiff = 0
            BEGIN
                SET @msg = 
                 'value for [maxdaysdiff] in registry table invalid or not found.'
                RAISERROR(@msg, 16, 1)
            END

            --set start and end date values
            SET @StartDt = '01/01/' + @LoadYear
            SET @EndDt = DATEADD(day, 364, @StartDt)

            --adjust start date if earlier dates not 
            --previously loaded, or also if later dates loaded
            EXEC GetUseStartDate @StartDt, @CalendarDaysMaxDiff, 
                 @StartDtAdj = @StartDtAdj OUT

            --adjust end date by the date diff
            SET @EndDtAdj = DATEADD(dd, @CalendarDaysMaxDiff, @EndDt)

            -- ok, load data now
            -- NOTE: table 2 - the AllDates table  
            --       - needs dates before and after the year boundary
            -- to allow complete fill in of table 3 (the AllDatesDiff table)
            EXEC dbo.PrintInfoMessage 'Data load initialization complete.'
        END TRY
        BEGIN CATCH
            EXECUTE ProcessError --TODO log this to an error table
            RETURN
        END CATCH

        -- table 1 - load primary table from stage - 1 row per holiday for the year
        BEGIN TRAN
        BEGIN TRY
            EXEC dbo.PrintInfoMessage 'Entered transaction.'
            EXEC dbo.PrintInfoMessage 'Starting HolidayDates table load.'
            EXEC LoadHolidayDates
            EXEC dbo.PrintInfoMessage 'HolidayDates table load completed.'

        -- table 2 - load the table with complete list
        --           of days for year including non-holidays.
            EXEC dbo.PrintInfoMessage 'Starting AllDates table load.'
            EXEC AllDatesLoad @StartDtAdj, @EndDtAdj
            EXEC dbo.PrintInfoMessage 'AllDates table load completed.'

        -- table 3 - load table with all the diff data
            EXEC dbo.PrintInfoMessage 'Starting AllDatesDiff table load.'
            EXEC AllDatesDiffLoad @StartDt, @EndDt, @CalendarDaysMaxDiff
            EXEC dbo.PrintInfoMessage 'AllDates table load completed.'

            EXEC dbo.PrintInfoMessage 'All tables loaded.'

            EXEC dbo.HolidayDatesStageDeleteAll
            EXEC dbo.PrintInfoMessage 'Holiday stage table data deleted.'

            EXEC dbo.DateLoadLogHistoryInsert @LoadYear
            EXEC dbo.PrintInfoMessage 'Load history log updated.'

            SET @Success = 'Y'

        END TRY

        BEGIN CATCH
            ROLLBACK
            EXECUTE ProcessError
            RETURN
        END CATCH

        --commit the transaction.
        BEGIN TRY
            COMMIT
            EXEC dbo.PrintInfoMessage 
                 'Transaction successfully committed. Data load complete.'
        END TRY
        BEGIN CATCH
            EXECUTE ProcessError
            RETURN
        END CATCH

        RETURN

    END

END

I made sure to wrap all Stored Procedures called from outside SQL Server in TRY...CATCH blocks, to ensure consistent error reporting to clients.

Notice the calls above to PrintInfoMessage as the procedure executes. I created this to send InfoMessage messages to the client application. The Stored Procedure is a simple wrapper for the PRINT method:

-- =============================================
-- Description: Execute a PRINT statement for purposes
--            of client capture by MessageInfo
-- NOTE: Use this sp to specifically intend to send an InfoMessage to a client
--        (eg an ASP.NET or Winforms ADO.NET client).
--        Inline PRINT statements are not good for this purpose as they are
--        likely to be considered debugging statements 
--        to be cleaned up (removed) by developers.
-- =============================================

ALTER PROCEDURE [dbo].[PrintInfoMessage]
    @msg VARCHAR(200)
AS

BEGIN
    DECLARE @CurrDate CHAR(23) --ODBC canonical with ms
    SET @CurrDate = CONVERT(CHAR, GETDATE(), 121)
    SET @msg = @CurrDate + ' : ' + @Msg

    --note: caller may trim first 26 chars of the message
    --      string to get the original message only

    PRINT @msg
    RETURN

END

Thinking about this, I'm not sure I like the approach I am using of having the client parse out the message string from the date, but it works.

4. Client ASP.NET Data Load/Maintenance Applet

There is not too much of special interest here, and I tried to do as much as possible at the database level in Transact-SQL. But you will have to use the ASP.NET webapp I created, or some kind of client .NET application, to perform the data load. A client app of some sort is needed because the process consists of basically three phases that cannot all be performed in Transact-SQL:

1. Get the holiday dates for the upcoming year by using the US Holiday Dates Web Service exposed at HolidayWebService.com (requires .NET Framework).
2. Fill a staging table with base holiday data, including the holiday dates.
3. Call the Stored Procedure DateLoad to perform the data load.

Here is the class containing the code used to pull the dates from the Holiday Web Service. To link to the service, I added the following web reference: http://www.holidaywebservice.com/Holidays/servicesAvailable_USHolidayDates.aspx. .NET does the rest, generating the plumbing code necessary, and from there, I can program against the service just like any other object.

using System;
using System.Data;
using System.Configuration;
using System.Diagnostics;
using System.Reflection;
using HolidayWbSrvConsumer.HolidayWS;
//request URL: http://www.holidaywebservice.com/Holidays/US/Dates/USHolidayDates.asmx

namespace Pwryn.HolidayServiceCli
{
    /// <summary>
    /// Author: Paul W. Reynolds (www.codeproject.com author: LittleDev06)
    /// Consumer class for public web service method
    /// located at holidaywebservice.com
    /// </summary>
    public class HolidayWebServiceConsumer
    {
        //member object
        private USHolidayDates m_HolService;

        public HolidayWebServiceConsumer()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /// <summary>
        /// Execute the web service methods from
        //  http://www.holidaywebservice.com to get actual dates for
        /// the holidays, and add them to a new column in our datatable object.
        /// The USHolidayDates service with an individual call for each
        /// named holiday seems to fiit our purposes best
        /// </summary>
        /// <param name="NextYear"></param>
        /// <param name="HolidaysToAddNextYear">This is a count.
        ///     If not zero, add in additional holidays for subsequent year. 
        ///     May be needed for to be sure we can do date differencing
        ///     for some time into the future</param>
        /// <param name="holtable">(ref) Incoming datatable object
        ///     preloaded with dateless holiday data (names, flags)</param>
        public void AddHolidayDates(ref DataTable holtable)
        {

            //set up the external holiday web service object
            m_HolService = new USHolidayDates();
            Object[] args = new Object[] { 0 };

            //find the method names in the data table
            //and execute web service method to get the dates
            foreach (DataRow row in holtable.Rows)
            {
                args[0] = row["DateYear"];
                row["HolidayDate"] = 
                  InvokeHolidayMethod(row["MethodName"].ToString(), args);
            }

            m_HolService.Dispose();
        }

       ///// <param name="args"></param>
        /// <summary>
        /// Get the holiday date for a given row by making the web method invokation
        /// </summary>
        /// <param name="holtable">Datatable we are operating on</param>
        /// <param name="row">row to update</param>
        /// <param name="args">list of arguments to pass to the Web method</param>
        private string InvokeHolidayMethod(string MethodName, Object[] args)
        {
            DateTime dtm;

            BindingFlags bndflags = BindingFlags.DeclaredOnly |
                BindingFlags.Public | BindingFlags.NonPublic |
                BindingFlags.Instance | BindingFlags.InvokeMethod;

            //UNDONE trap for TargetInvocationException if server
            //is down or no Web connectivity, or other general exception
            dtm = (DateTime)m_HolService.GetType().InvokeMember(MethodName, bndflags, 
                            null, (object)m_HolService, args);

            return dtm.ToShortDateString();

        }
    }
}

As the name suggests, this service is limited to US holidays. I stored the method names called (one for each holiday per the interface to USHolidayDates) in the SQL Server table HolidayNames. Then I call each of the methods iteratively using Reflection and the InvokeMember method (CallByName in VB6 was a little easier to use).

Here is the code that executes DateLoad:

/// <summary>
/// Call the Transact SQL sp to load all holiday, date, and aging data needed
/// into the appropriate tables
/// </summary>
/// <param name="LoadYear">The year of date data to load</param>
public void DateLoad(string LoadYear)
{
    try
    {
        m_InfoMessageItemQueue = new Queue<InfoMessageItem>();

        SqlCommand cmm = new SqlCommand();

        this.SqlCommandInitProps(cmm, MethodBase.GetCurrentMethod().Name);
        this.SqlCommandSetConnection(cmm);
        SqlParameter prmLoadYear = cmm.Parameters.Add("@LoadYear", SqlDbType.VarChar, 4);
        prmLoadYear.Value = LoadYear;
        SqlParameter prmSuccess = cmm.Parameters.Add("@Success", SqlDbType.Char, 1);
        prmSuccess.Direction = ParameterDirection.Output;

        int ret = cmm.ExecuteNonQuery();
    }
    finally
    {
        if (m_Connection != null && m_Connection.State != ConnectionState.Closed)
        {
            m_Connection.Close();
        }
    }

}

The generic Queue variable is used to receive the InfoMessage messages sent from the Stored Procedure (see above). The messages are caught by the following event handler:

/// <summary>
/// This event handler captures process messages
/// sent while the stored procedures are executing.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SqlMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
{
    foreach (SqlError err in e.Errors)
    {

        //only want messages specifically sent
        //to us via PrintInfoMessage stored procedure.
        //Other system messages are ignored;
        //errors or exceptions are not handled here
        //but via standard .NET exception handling.
        InfoMessageItem imi;
        if (err.Procedure == "PrintInfoMessage")
        {
            //please see sproc [dbo].[PrintInfoMessage]
            //for how the message is constructed.
            imi = new InfoMessageItem();
            imi.MessageDateTime = err.Message.Substring(0, 23);
            imi.Message = err.Message.Substring(26, err.Message.Length - 26);
            m_InfoMessageItemQueue.Enqueue(imi);
            Debug.WriteLine(imi.Message);
        }
    }
}

I tried to implement the call to DateLoad asynchronously, but was not having success in making it work with ASP (I'm sure it is doable with AJAX-style coding). As can be seen above, I do capture messages sent to the client during the running process (see the Stored Procedure PrintInfoMessage). But given the load is running synchronously, I perhaps may as well have just saved that information to a database log table, then read the table contents after the commit.

Note: The holiday Web Service access piece and the database access piece live in their own class DLL modules (DateAgingAdminData.dll and HolidayWbSrvConsumer.dll), so if you wanted to move my rather clunky and slightly ugly ASP.NET app into a cleaner WinForms front-end and link to those two DLLs, it would not be too hard to discard my ASP and write your own client. I know for sure executing DateLoad async, to give the user a running status of what is happening, would be much easier to do inside a WinApp; even I could do it easily (William Vaughn's latest Hitchhiker's Guide book is a terrific reference).

5. Summary

So what do we have at this point? We have a database table filled with a lot of dates and date difference data:

To make it useful, we will have to apply views on top of it, expose those views, and access them. In a production module such as this, besides being sure beforehand that the design of the system matches business requirements, I'd be sure to implement auditing of data and logging the data processes run. I would also limit access to the database, especially add and update access to the base tables, as much as possible. Even select functionality could generally be limited to user friendly views (you will see some more of these in phases 2 and 3).

6. Future Plans

In phase 2, I plan to implement an ASP.NET Web Service that executes common business date functions such as NextBusinessDay, IsBusinessDay, IsHoliday, etc., against this database. It will use the pre-compiled logic that exists in the loaded data. For phase 3 (this is the really useful piece), I will add some views that include derived columns by using CASE statements within the SQL - then create reports that query the views joined against large rowsets of sample data. I don't yet know if I am going to use Reporting Services or Crystal Reports for my sample reports. Hopefully the reports will look nicer than my ASP.NET front end here.

History

• 2007-05-28: Posted on CodeProject.
• 2007-06-02: Solution file pointed to E drive as project drive. Modified solution file to point to C drive, so all projects should load correctly when zipped to a C drive. Also changed primary keys on tables to unclustered indexes to reduce database size, as they should not be needed.
• 2007-07-17:
• ASP.NET - Modified Maintenance screen to allow user to select year to load.
• Database - Modified the Stored Procedure AllDatesDiffLoad to load *all* dates into the DateDiff table for start dates - not just business days, and to not load dates where start date is same as end date.