Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 4, 2005

SQL Server: Customized Calendar Tables

By Muthusamy Anantha Kumar aka The MAK

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 =@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')


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date