USE master
go
 
IF  EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tCalendar')
BEGIN
DROP TABLE tCalendar
END
go
 
CREATE TABLE tCalendar (
DateID INT IDENTITY(1,1) CONSTRAINT tCalendar_PK PRIMARY KEY CLUSTERED,
DATE DATETIME,
Holiday BIT DEFAULT 0,
Workday BIT DEFAULT 0)
go
CREATE UNIQUE NONCLUSTERED INDEX
tCalendar_date_N_Idx ON tCalendar(DATE)
go
 
--Populate all days
 
DECLARE @n INT
DECLARE @maxn INT
DECLARE @begindate DATETIME
SET @n =1
SET @maxn=36500    -- Number of days added to the calendar
SET @begindate =CONVERT(DATETIME,'01/01/1995')
-- Initial date for the first run is todays date
-- or Jan 1st
SET @begindate =@begindate -1
 
WHILE @n <= @maxn
BEGIN
INSERT INTO tCalendar(DATE) SELECT @begindate+@n
SET @n=@n+1
END
 
--update the holiday and workday flags
 
go
UPDATE tCalendar
SET holiday=1 WHERE DATENAME(dw,DATE) in ('Saturday','Sunday')
go
UPDATE tCalendar
SET workday=1 WHERE holiday=0
go
 
SELECT * FROM tCalendar