SQL Server: Customized Calendar Tables

May 4, 2005

SQL Server job schedule configuration is limited to a one-time occurrence: daily, monthly and weekly. [Refer Fig 1.0] There are many occasions when you need to run a job on certain dates in a year. Many Finance and pharmaceuticals companies have End of Month processing, End of Week processing, etc. where batch jobs need to be executed on a certain day of the month and/or week. Some companies have to execute certain jobs only on holidays while others require procedures to be executed if yesterday was a holiday.

This article examines how to create customized calendar tables and populate data.

Apply custom calendar in SQL Server jobs by using the user-defined function:

Creating customized calendar tables and populate data

Step 1

Execute the following SQL Statement:

use master
go
create table Corporate_Calendar (
Dateid int identity(1,1) constraint Corporate_Calendar_PK primary key CLUSTERED, 
Date datetime, 
Holiday bit default 0, 
Workday bit default 0, 
EOMProcessdate bit default 0,
EOWProcessdate bit default 0,
SpecialProcessdate bit default 0,
Datestamp datetime default getdate())
go
Create UNIQUE nonclustered index 
Corporate_Calendar_date_N_Idx on Corporate_Calendar(Date)
go

Step 2

Populate all dates in the calendar table. Execute the statement below after updating the number of days and begin date. This statement will populate 365 days for the year 2005. Refer Fig 1.1

--Populate all days
use master
go

declare @n int
declare @maxn int
declare @begindate datetime
set @n =1
set @maxn=365	-- Number of days added to the calendar
set @begindate =convert(datetime,'01/01/2005')
		-- Initial date for the first run is todays date
		-- or Jan 1st 
set @begindate =@begindate -1

while @n <= @maxn
begin
insert into Corporate_Calendar(date) select @begindate+@n
set @n=@n+1
end

--select * from Corporate_Calendar

Step 3

Update the holiday flag for all weekends and the workday flag for all weekdays in the calendar table.

use master
go
update Corporate_Calendar 
set holiday=1 where datename(dw,date) in ('Saturday','Sunday')
go
update Corporate_Calendar 
set workday=1 where holiday=0
go

Step 4

Get information about End of Week, End of Month and Special processing dates from the business and update the calendar table accordingly. Example:

update Corporate_Calendar 
set EOWProcessdate =1 where date ='1/17/2005'

Step 5

Get information about other holidays from the business and update the calendar table accordingly. Example:

Use master
go
update Corporate_Calendar 
set holiday=1 where date = '12/25/2005' -- Xmas day
go
update Corporate_Calendar 
set holiday=1 where date = '3/25/2005' -- Easter
go

The calendar table should now appear similar to the one shown below. Refer Fig 1.2

Step 6

Create the user defined function udf_isProcessDate, by executing the following command.

use master
go
create function dbo.udf_isProcessDate (@date datetime, @Type varchar(10))
returns bit
begin
declare @x bit
set @x=NULL
If @type='Holiday' 
select @x = holiday from master.dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='Workday' 
select @x = WorkDay from master.dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='EOM' 
select @x = EOMProcessdate from master.dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='EOW' 
select @x = EOWProcessdate from master.dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='Special' 
select @x = SpecialProcessdate from master.dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
return @x
end
go

--select master.dbo.udf_isProcessDate(getdate(),'WorkDay')