SQL Server: Customized Calendar Tables

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

Note: Please change 365 to
the actual number of days you want to populate the calendar table.


–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 [email protected] -1

while @n <= @maxn
begin
insert into Corporate_Calendar(date) select @[email protected]
set @[email protected]+1
end

–select * from Corporate_Calendar



Fig 1.1

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



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’)

Latest Articles