SQL Server: Customized Calendar Tables

Wednesday May 4th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

MAK explains how to create customized calendar tables, populate data and how to apply a custom calendar in SQL Server jobs by using the user-defined function udf_isProcessDate.

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:

Click for larger image

Fig 1.0

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 =@begindate -1

while @n <= @maxn
begin
insert into Corporate_Calendar(date) select @begindate+@n
set @n=@n+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')

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

To execute a procedure on all of the end of week processing dates at 11 PM:

Step 1

Create a job with the job scheduler to run daily at 11 PM, as shown below. Refer Fig 1.3

Click for larger image

Fig 1.3

Step 2

Update the job steps as shown below. Refer Fig 1.4

Click for larger image

Fig 1.4

Step 3

When the job runs during the days that are not End of week processing dates, you will see the following message in the job history. Refer Fig 1.5

Skipped. Not a END of week processing day


Fig 1.5

4. When the job runs during End of week processing date then you will see the following message in the job history. Refer Fig 1.6

Processed


Fig 1.6

The same function can be used for various purposes. Example:

--Is Today a Workday
if master.dbo.udf_isProcessDate(getdate(),'WorkDay')
begin
exec Myproc
end

--Is Today a End of Month processing date
if master.dbo.udf_isProcessDate(getdate(),'EOM')
begin
exec Myproc
end

--Is Today a holiday
if master.dbo.udf_isProcessDate(getdate(),'Holiday')
begin
exec Myproc
end

--Is Today a special processing date
if master.dbo.udf_isProcessDate(getdate(),'Special')
begin
exec Myproc
end

--Is Today a end of week processing date?
if master.dbo.udf_isProcessDate(getdate(),'EOW')
begin
exec Myproc
end

--Was Yesterday a Hoilday?
if master.dbo.udf_isProcessDate(getdate()-1,'Holiday')
begin
exec Myproc
end

Note: Feel free to modify the table Corporate_Calendar and the function udf_isProcessDate according to your requirements. Additional columns in the table and additional parameters in the function are sometimes required.

Conclusion

This article has discussed how to create customized calendar tables and populate data. It has also explained how to apply custom calendar in SQL Server jobs by using the user-defined function udf_isProcessDate.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved