This article continues building on the calendar series. You can read Part I and Part II for background.

I recently had a client that wanted to change a date based business rule. They run the business based on weekly statistics. When I first worked for this client there was an Access database that one of the owners had created to keep track of various types of activities - calls by customers and prospects, appointments made, estimates and sales.

They use the concept of a week ending date (not uncommon in the business world) as the cutoff for certain statistics.

I converted the Access tables to SQL Server2005 and set up the front end to use a week based calendar table to determine in which week ending date the activity fell. The front end has now grown to a web based interface, but all tables and logic remain the same.

In this article I will be using a stripped down version of the actual data tables to show how easy it is to change date based business rules using a calendar table.

The client wanted to change their business week from Monday through Sunday to Sunday through Saturday. All reports and history had to remain the same, but going forward they wanted the week ending date to be Saturday rather than Sunday.

Here is a simplified version of their calendar table. I'm only using a 2 year table for this:

-- Create and populate the CalWeek table.
IF OBJECT_ID('CalWeek') IS NOT NULL
 DROP TABLE CalWeek
GO
CREATE TABLE CalWeek
( WeekID INT IDENTITY(1, 1)
, WkStart SMALLDATETIME
, WkEnd SMALLDATETIME
)
GO
ALTER TABLE CalWeek ADD CONSTRAINT PK_CalWeek
PRIMARY KEY CLUSTERED (WeekID) WITH (FILLFACTOR=100)
GO
-- Add records from the 1st week ending of 2010
-- to the last week of 2011
SET NOCOUNT ON;
DECLARE
 @WkStart SMALLDATETIME
, @WkEnd SMALLDATETIME
SET @WkStart = '2009-12-28' -- Last Monday of Dec 2009
SET @WkEnd = '2010-01-03'   -- First Sunday of Jan 2010
WHILE @WkStart < '2011-12-31'
 BEGIN
 INSERT INTO CalWeek
 ( WkStart, WkEnd )
 SELECT @WkStart, @WkEnd
 SET @WkStart = DATEADD(day, 7, @WkStart)
 SET @WkEnd = DATEADD(day, 7, @WkEnd)
 END
GO
CREATE UNIQUE INDEX CalWeek_StartEnd ON CalWeek (WkStart, WkEnd)
CREATE UNIQUE INDEX CalWeek_WeekEnd ON CalWeek (WkEnd)
GO

This is a stripped down version of the Activity table:

-- Create the Activity table.
IF OBJECT_ID('Activity') IS NOT NULL
 DROP TABLE Activity
GO
CREATE TABLE dbo.Activity
( ActivityID INT IDENTITY(1,1) NOT NULL
, ActivityDate DATETIME NULL
, WeekEndingDate DATETIME NULL
, ActivitySource INT NULL
, ActivityType INT NULL
, SalesmanID INT NULL
 ,SaleAmount MONEY NULL
)
GO
ALTER TABLE dbo.Activity ADD CONSTRAINT PK_Activity
PRIMARY KEY CLUSTERED (ActivityID)
GO
CREATE NONCLUSTERED INDEX Activity_ActivityDate ON Activity (ActivityDate)
CREATE NONCLUSTERED INDEX Activity_WeekEnding ON Activity (WeekEndingDate)
GO

Here are the related tables:

-- Create the related ActivitySource table.
IF OBJECT_ID('ActivitySource') IS NOT NULL
 DROP TABLE ActivitySource
GO
-- These are promo type sources that create calls from customers
-- and prospects.
CREATE TABLE ActivitySource
( SourceID INT PRIMARY KEY CLUSTERED
, SourceName VARCHAR(50)
)
GO
INSERT INTO ActivitySource
( SourceID, SourceName )
SELECT 0, 'Radio Add 1' UNION ALL
SELECT 1, 'Mailer 1' UNION ALL
SELECT 2, 'Web Promo 1' UNION ALL
SELECT 3, 'Billboard 1'
GO
-- Create the related ActivityTypes table.
IF OBJECT_ID('ActivityTypes') IS NOT NULL
 DROP TABLE ActivityTypes
GO
CREATE TABLE ActivityTypes
( AcivityTypeID INT PRIMARY KEY CLUSTERED
, ActivityType VARCHAR(20)
)
GO
INSERT INTO ActivityTypes
( AcivityTypeID, ActivityType )
SELECT 0, 'Call Received' UNION ALL
SELECT 1, 'Appt Made' UNION ALL
SELECT 2, 'Estimate' UNION ALL
SELECT 3, '2nd Estimate'
GO
-- Create the related SalesPersons table.
IF OBJECT_ID('SalesPersons') IS NOT NULL
  DROP TABLE SalesPersons
GO
CREATE TABLE SalesPersons
( SalesmanID    INT PRIMARY KEY CLUSTERED
, SalesPerson   VARCHAR(50)
)
GO
INSERT INTO SalesPersons
(SalesmanID, SalesPerson )
SELECT 0, 'Joe Blow' UNION ALL
SELECT 1, 'Jane Doe' UNION ALL
SELECT 2, 'John Smith' UNION ALL
SELECT 3, 'Mary Jones'
-- Initially populate the Activity table.
-- The dates are pure dates (no time) between 1 Jan 2010 and 15 Jan 2011.
-- The ActivitySource is a random number between 0 and 3
-- Same for ActivityType and SalesmanID
DECLARE
 @Lower INT
, @Upper INT
SET @Lower = 40177 -- 1 Jan 2010
SET @Upper = 40571 -- 30 Jan 2011
INSERT INTO Activity
( ActivityDate, ActivitySource, ActivityType, SalesmanID )
SELECT TOP 5000
  CONVERT(DATETIME, ROUND(((@Upper - @Lower -1) 
  * RAND(CHECKSUM(NEWID())) + @Lower), 0))
, ABS(CHECKSUM(NEWID())) & 3
, ABS(CHECKSUM(NEWID())) & 3
, ABS(CHECKSUM(NEWID())) & 3
FROM sys.syscolumns S1
CROSS JOIN sys.syscolumns S2

The table now has 5000 random activities with random activity types, random activity sources and random sales persons. When the records were initially created in Access or the web front end, the WeekendingDate was already populated by using a query like:

SELECT WkEnd FROM CalWeek WHERE {Today's Date} BETWEEN WkStart And WkEnd

Since we only have a test set here we have to set the WeekendingDate in the Activity table. Normally this was done when the Activity record was originally inserted using a query like the one above:

-- Update the Activity table with a Week Ending Date
UPDATE A
SET
 A.WeekEndingDate = CW.WkEnd
FROM Activity A
INNER JOIN CalWeek CW ON
 A.ActivityDate BETWEEN CW.WkStart And CW.WkEnd

Some activities involve sales. We'll fill in the applicable activities with a sales amount:

-- Create some sales for activities 2 and 3 (estimate and 2nd estimate)
UPDATE Activity
SET
 SaleAmount = ABS(CHECKSUM(NEWID())) & 1000
WHERE ActivityType IN (2, 3)

Now we have some test data. A simplified, but typical query on this data would look like:

-- The most effective Source of promotion in the last 4 weeks for Calls.
SELECT
 S.SourceName, COUNT(*) AS Calls
FROM
 (SELECT TOP 4 WkEnd FROM CalWeek
 WHERE WkEnd <= DATEADD(DAY, DATEDIFF(day, 0, GETDATE()), 0)
 ORDER BY WkEnd DESC
 ) AS Wks
INNER JOIN Activity A ON
 A.WeekEndingDate = Wks.WkEnd
INNER JOIN CalWeek CW ON
 A.WeekEndingDate = CW.WkEnd
INNER JOIN ActivitySource S ON
 A.ActivitySource = S.SourceID
WHERE
 A.ActivityType = 0 -- Call In
GROUP BY S.SourceName

This gives a result set that looks like:

Looks like Billboards and Radio add 1 produced the most calls.

The sales amount in the Activities table is used for commissions and such. A typical query to give the last 4 weeks of sales by sales person might look like this:

-- Last 4 weeks of sales by salesperson
-- Use a variable for the date to allow for testing
-- for date ranges in the past.
DECLARE
 @Wk SMALLDATETIME
SET @Wk = DATEADD(DAY, DATEDIFF(day, 0, GETDATE()), 0)
; WITH TheWeeks AS  -- Get the last 4 complete weeks.
( SELECT TOP 4 WeekID, WkEnd
 FROM CalWeek
 WHERE WkEnd <= @Wk
 ORDER BY WkEnd DESC
), WksNum AS        -- Number them for readability
( SELECT WkEnd, ROW_NUMBER() OVER (ORDER BY WkEnd) AS WkNum
 FROM TheWeeks
)
SELECT
 SP.SalesPerson, A.WeekEndingDate, WN.WkNum
, COUNT(*) AS Appointments
, SUM(A.SaleAmount) AS Sales
FROM
Activity A
INNER JOIN WksNum WN ON
 A.WeekEndingDate = WN.WkEnd
INNER JOIN ActivityTypes AT ON
 A.ActivityType = AT.AcivityTypeID
INNER JOIN SalesPersons SP ON
 A.SalesmanID = SP.SalesmanID
WHERE
 A.ActivityType IN (2, 3)
GROUP BY SP.SalesPerson, A.WeekEndingDate, WN.WkNum
ORDER BY WeekEndingDate, SalesPerson

And the result set would look like:

Now comes the part where the client wants to change business rules. Instead of Monday through Sunday, they now want their business week to be Sunday through Saturday starting with week ending 16 Jan 2011. This means week ending date 16 Jan 2011 needs to change to 15 Jan 2011 (making it a short week) and all subsequent weeks need to be adjusted.

Using the calendar table technique I did this in 3 queries:

-- WkEnd 2011-01-16 (Sunday) becomes 2011-01-15 (Saturday)
UPDATE CalWeek
SET WkEnd = '2011-01-15'
WHERE WkEnd = '2011-01-16'
-- Everything else gets moved back 1 day.
UPDATE CalWeek
SET WkStart = DATEADD(day, -1, WkStart)
, WkEnd = DATEADD(day, -1, WkEnd)
WHERE WkStart >= '2011-01-17'
-- Change the Week Ending date in Activity
-- to match the calendar table.  This handles anything already entered
-- with the old Weekending Date for the current week.
UPDATE A
SET
 A.WeekEndingDate = CW.WkEnd
FROM Activity A
INNER JOIN CalWeek CW ON
 A.ActivityDate BETWEEN CW.WkStart And CW.WkEnd
WHERE A.WeekEndingDate <> CW.WkEnd

After running the above update we now run the sales query from above. The new result set looks like:

Notice the change in week ending date for week #2 and subsequent weeks. All numbers have been adjusted to the new week ending date and no history is changed since all report queries use the calendar table to determine the week to which the activity belongs.

Since I insisted that NO week ending calculations were to be done in any front end (Access or Web), all new activity records will have the correct week ending date.

I hope this can be us use to someone.

Todd Fifield