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 join
s that return NULL
values. NULL
s 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.
ALTER PROCEDURE [dbo].[DateLoad]
@LoadYear VARCHAR(4),
@Success CHAR(1) OUT
AS
BEGIN
DECLARE @Found CHAR(1)
DECLARE @NumRows INT
DECLARE @StartDt SMALLDATETIME
DECLARE @EndDt SMALLDATETIME
DECLARE @StartDtAdj SMALLDATETIME
DECLARE @EndDtAdj SMALLDATETIME
DECLARE @CalendarDaysMaxDiff SMALLINT
DECLARE @msg VARCHAR(200)
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
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
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
EXEC dbo.GetHolidayDateStageRowCount @NumRows = @NumRows OUT
IF @NumRows = 0
BEGIN
SET @msg = 'Holiday stage table is not loaded.'
RAISERROR(@msg, 16, 1)
END
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 @StartDt = '01/01/' + @LoadYear
SET @EndDt = DATEADD(day, 364, @StartDt)
EXEC GetUseStartDate @StartDt, @CalendarDaysMaxDiff,
@StartDtAdj = @StartDtAdj OUT
SET @EndDtAdj = DATEADD(dd, @CalendarDaysMaxDiff, @EndDt)
EXEC dbo.PrintInfoMessage 'Data load initialization complete.'
END TRY
BEGIN CATCH
EXECUTE ProcessError
RETURN
END CATCH
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.'
EXEC dbo.PrintInfoMessage 'Starting AllDates table load.'
EXEC AllDatesLoad @StartDtAdj, @EndDtAdj
EXEC dbo.PrintInfoMessage 'AllDates table load completed.'
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
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:
ALTER PROCEDURE [dbo].[PrintInfoMessage]
@msg VARCHAR(200)
AS
BEGIN
DECLARE @CurrDate CHAR(23)
SET @CurrDate = CONVERT(CHAR, GETDATE(), 121)
SET @msg = @CurrDate + ' : ' + @Msg
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:
- Get the holiday dates for the upcoming year by using the US Holiday Dates Web Service exposed at HolidayWebService.com (requires .NET Framework).
- Fill a staging table with base holiday data, including the holiday dates.
- 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;
namespace Pwryn.HolidayServiceCli
{
public class HolidayWebServiceConsumer
{
private USHolidayDates m_HolService;
public HolidayWebServiceConsumer()
{
}
public void AddHolidayDates(ref DataTable holtable)
{
m_HolService = new USHolidayDates();
Object[] args = new Object[] { 0 };
foreach (DataRow row in holtable.Rows)
{
args[0] = row["DateYear"];
row["HolidayDate"] =
InvokeHolidayMethod(row["MethodName"].ToString(), args);
}
m_HolService.Dispose();
}
private string InvokeHolidayMethod(string MethodName, Object[] args)
{
DateTime dtm;
BindingFlags bndflags = BindingFlags.DeclaredOnly |
BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance | BindingFlags.InvokeMethod;
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
:
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:
private void SqlMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
{
foreach (SqlError err in e.Errors)
{
InfoMessageItem imi;
if (err.Procedure == "PrintInfoMessage")
{
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.