February 24, 2005 at 11:12 am
Hello all.
I would like to create a calendar table within SQL Server, include holidays and populate it for a few years. Does anyone have a set of statements they have used before for something like this?
February 24, 2005 at 12:12 pm
I made a monthly calender...c if it helps.u can probably build further on this.omit the parts which r necessary in ur case..
CREATE PROCEDURE MonthlyCalendar
@year int,
@month int,
@program_id int
as
begin
declare @fdm int
declare @fdmstr varchar(10)
declare @datestr varchar(10)
declare @date1 datetime
declare @M1 datetime
declare @M2 datetime
declare @M3 datetime
declare @M4 datetime
declare @M5 datetime
declare @T1 datetime
declare @T2 datetime
declare @T3 datetime
declare @T4 datetime
declare @T5 datetime
declare @W1 datetime
declare @W2 datetime
declare @W3 datetime
declare @W4 datetime
declare @W5 datetime
declare @TH1 datetime
declare @TH2 datetime
declare @TH3 datetime
declare @TH4 datetime
declare @TH5 datetime
declare @F1 datetime
declare @F2 datetime
declare @F3 datetime
declare @F4 datetime
declare @F5 datetime
declare @S1 datetime
declare @S2 datetime
declare @S3 datetime
declare @S4 datetime
declare @S5 datetime
declare @Su1 datetime
declare @Su2 datetime
declare @Su3 datetime
declare @Su4 datetime
declare @Su5 datetime
declare @AM1 int
declare @AM2 int
declare @AM3 int
declare @AM4 int
declare @AM5 int
declare @AT1 int
declare @AT2 int
declare @AT3 int
declare @AT4 int
declare @AT5 int
declare @AW1 int
declare @AW2 int
declare @AW3 int
declare @AW4 int
declare @AW5 int
declare @ATH1 int
declare @ATH2 int
declare @ATH3 int
declare @ATH4 int
declare @ATH5 int
declare @AF1 int
declare @AF2 int
declare @AF3 int
declare @AF4 int
declare @AF5 int
set @datestr=cast(@month as varchar(2))+'/01/'+cast(@year as varchar(4))
set @date1=convert(datetime,@datestr,101)
set @fdm=datepart(dw,@date1)
set @fdmstr=datename(dw,@date1)
--print @date1
--print @fdmstr
if @fdmstr='Monday'
begin
set @M1 =@date1
set @T1 =dateadd(dd,1,@date1)
set @W1 =dateadd(dd,2,@date1)
set @TH1 =dateadd(dd,3,@date1)
set @F1 =dateadd(dd,4,@date1)
set @S1 =dateadd(dd,5,@date1)
set @Su1 =dateadd(dd,6,@date1)
end
if @fdmstr='Tuesday'
begin
set @M1 =NULL
set @T1 =@date1
set @W1 =dateadd(dd,1,@date1)
set @TH1 =dateadd(dd,2,@date1)
set @F1 =dateadd(dd,3,@date1)
set @S1 =dateadd(dd,4,@date1)
set @Su1 =dateadd(dd,5,@date1)
end
if @fdmstr='Wednesday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =@date1
set @TH1 =dateadd(dd,1,@date1)
set @F1 =dateadd(dd,2,@date1)
set @S1 =dateadd(dd,3,@date1)
set @Su1 =dateadd(dd,4,@date1)
end
if @fdmstr='Thursday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =@date1
set @F1 =dateadd(dd,1,@date1)
set @S1 =dateadd(dd,2,@date1)
set @Su1 =dateadd(dd,3,@date1)
end
if @fdmstr='Friday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =NULL
set @F1 =@date1
set @S1 =dateadd(dd,1,@date1)
set @Su1 =dateadd(dd,2,@date1)
end
if @fdmstr='Saturday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =NULL
set @F1 =NULL
set @S1 =@date1
set @Su1 =dateadd(dd,1,@date1)
end
if @fdmstr='Sunday'
begin
set @M1 =NULL
set @T1 =NULL
set @W1 =NULL
set @TH1 =NULL
set @F1 =NULL
set @S1 =NULL
set @Su1 =@date1
end
SET @M2=DATEADD(DD,1,@SU1)
SET @M3=DATEADD(DD,7,@M2)
SET @M4=DATEADD(DD,7,@M3)
SET @M5=DATEADD(DD,7,@M4)
IF Month(@M5) <> @month
Set @M5=Null
SET @T2=DATEADD(DD,1,@M2)
SET @T3=DATEADD(DD,7,@T2)
SET @T4=DATEADD(DD,7,@T3)
SET @T5=DATEADD(DD,7,@T4)
IF Month(@T5) <> @month
Set @T5=Null
SET @W2=DATEADD(DD,1,@T2)
SET @W3=DATEADD(DD,7,@W2)
SET @W4=DATEADD(DD,7,@W3)
SET @W5=DATEADD(DD,7,@W4)
IF Month(@W5) <> @month
Set @W5=Null
SET @TH2=DATEADD(DD,1,@W2)
SET @TH3=DATEADD(DD,7,@TH2)
SET @TH4=DATEADD(DD,7,@TH3)
SET @TH5=DATEADD(DD,7,@TH4)
IF Month(@TH5) <> @month
Set @TH5=Null
SET @F2=DATEADD(DD,1,@TH2)
SET @F3=DATEADD(DD,7,@F2)
SET @F4=DATEADD(DD,7,@F3)
SET @F5=DATEADD(DD,7,@F4)
IF Month(@F5) <> @month
Set @F5=Null
select @AM1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m1)=0 )
select @AM2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m2)=0 )
select @AM3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m3)=0 )
select @AM4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m4)=0 )
select @AM5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m5)=0 )
select @AT1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t1)=0 )
select @AT2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t2)=0 )
select @AT3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t3)=0 )
select @AT4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t4)=0 )
select @AT5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t5)=0 )
select @AW1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w1)=0 )
select @AW2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w2)=0 )
select @AW3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w3)=0 )
select @AW4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w4)=0 )
select @AW5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w5)=0 )
select @ATH1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th1)=0 )
select @ATH2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th2)=0 )
select @ATH3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th3)=0 )
select @ATH4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th4)=0 )
select @ATH5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th5)=0 )
select @AF1=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f1)=0 )
select @AF2=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f2)=0 )
select @AF3=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f3)=0 )
select @AF4=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f4)=0 )
select @AF5=count(distinct(student_id)) FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f5)=0 )
SELECT convert(char(7),@M1,6) AS 'M1',convert(char(7),@M2,6) AS 'M2',convert(char(7),@M3,6) AS 'M3',convert(char(7),@M4,6) AS 'M4',convert(char(7),@M5,6) AS 'M5',
convert(char(7),@T1,6) AS 'T1',convert(char(7),@T2,6) AS 'T2',convert(char(7),@T3,6) AS 'T3',convert(char(7),@T4,6) AS 'T4',convert(char(7),@T5,6) AS 'T5',
convert(char(7),@W1,6) AS 'W1',convert(char(7),@W2,6) AS 'W2',convert(char(7),@W3,6) AS 'W3',convert(char(7),@W4,6) AS 'W4',convert(char(7),@W5,6) AS 'W5',
convert(char(7),@TH1,6) AS 'TH1',convert(char(7),@TH2,6) AS 'TH2',convert(char(7),@TH3,6) AS 'TH3',convert(char(7),@TH4,6) AS 'TH4',convert(char(7),@TH5,6) AS 'TH5',
convert(char(7),@F1,6) AS 'F1',convert(char(7),@F2,6) AS 'F2',convert(char(7),@F3,6) AS 'F3',convert(char(7),@F4,6) AS 'F4',convert(char(7),@F5,6) AS 'F5',
@AM1 AS 'AM1',@AM2 AS 'AM2',@AM3 AS 'AM3',@AM4 AS 'AM4',@AM5 AS 'AM5',
@AT1 AS 'AT1',@AT2 AS 'AT2',@AT3 AS 'AT3',@AT4 AS 'AT4',@AT5 AS 'AT5',
@AW1 AS 'AW1',@AW2 AS 'AW2',@AW3 AS 'AW3',@AW4 AS 'AW4',@AW5 AS 'AW5',
@ATH1 AS 'ATH1',@ATH2 AS 'ATH2',@ATH3 AS 'ATH3',@ATH4 AS 'ATH4',@ATH5 AS 'ATH5',
@AF1 AS 'AF1',@AF2 AS 'AF2',@AF3 AS 'AF3',@AF4 AS 'AF4',@AF5 AS 'AF5'
END
GO
-Sudha
SSM
February 25, 2005 at 7:05 am
See if the following function does the trick for you
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FloatingDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FloatingDate]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday_List]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Holiday_List]
GO
---------------------------------------------------------------------------------
-- Function to return the X occurence of a particular weekday with a month/year
---------------------------------------------------------------------------------
CREATE FUNCTION dbo.FloatingDate(@Occur INT,@WeekDay INT,@Month INT,@Year INT)
RETURNS
SMALLDATETIME
AS
BEGIN
DECLARE @Result SMALLDATETIME
DECLARE @StartDate SMALLDATETIME
DECLARE @DayOfWeek INT
-- Get Starting date, which is first day of the month
SET @StartDate = CONVERT(SmallDateTime,CAST(@Month AS VARCHAR(2))+'/1/'+CAST(@Year AS VARCHAR(4)))
SET @DayOfWeek = DatePart(dw,@StartDate) -- Falls on what day?
IF @DayOfWeek < @weekDay
SET @StartDate = DateAdd(d,@weekDay-@DayOfWeek,@StartDate) -- Adjust to requested day of week
IF @DayOfWeek > @weekDay
SET @StartDate = DateAdd(d,@DayOfWeek-@weekDay,@StartDate) -- Adjust to requested day of week
SET @Result = DateAdd(wk,@Occur-1,@StartDate) -- Get X occurrence of day
RETURN @Result
END
GO
---------------------------------------------------------------------------------
-- Returns a virtual table containing all holidays for a given year
---------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[Holiday_List] (@nYear INT)
RETURNS @Holidays TABLE
(Holiday_name VARCHAR(32),
Holiday_date SMALLDATETIME
)
AS
BEGIN
-- Calculate Easter Sunday
DECLARE @g INT
DECLARE @C INT
DECLARE @h INT
DECLARE @i INT
DECLARE @j-2 INT
DECLARE @l INT
DECLARE @Month INT
DECLARE @Day INT
DECLARE @Easter SMALLDATETIME
DECLARE @WorkDT SMALLDATETIME
-- Bizarre Algorithm to determine Easter Sunday
SET @g = @nYear % 19
SET @C = @nYear / 100
SET @h = ((@c - (@c / 4) - ((8 * @C + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j-2 = ((@nYear + (@nYear / 4) + @i + 2 - @C + (@c / 4)) % 7)
SET @l = @i - @j-2
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))
------------------------------------------------------------------------------------------------
-- Add Easter Sunday to holiday list, and get holidays based around Easter
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Easter',@Easter)
-- Good Friday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Good Friday',DateAdd(d,-2,@Easter))
-- Palm Sunday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Palm Sunday',DateAdd(ww,-1,@Easter))
-------------------------------------------------------------------------------------------------
-- Fixed date holidays are loaded next
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('New Year''s Day',CONVERT(SmallDateTime,'1/1/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Groundhog Day',CONVERT(SmallDateTime,'2/2/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Lincoln''s Birthday',CONVERT(SmallDateTime,'2/12/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Valentines Day',CONVERT(SmallDateTime,'2/14/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('St. Patrick''s Day',CONVERT(SmallDateTime,'3/17/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('April Fools Day',CONVERT(SmallDateTime,'4/1/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Flag Day',CONVERT(SmallDateTime,'6/14/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1776 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Independence Day',CONVERT(SmallDateTime,'7/4/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1958 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Boss''s Day',CONVERT(SmallDateTime,'10/16/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Halloween',CONVERT(SmallDateTime,'10/31/'+CAST(@nYear AS VARCHAR(4))))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Christmas',CONVERT(SmallDateTime,'12/25/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear >=1966 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Kwanzaa',CONVERT(SmallDateTime,'12/26/'+CAST(@nYear AS VARCHAR(4))))
-- Holidays that full on the same day of the week (based on the year they were officially established)
IF @nYear>=1983 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Martin Luther King Day',dbo.FloatingDate(3,2,1,@nYear)) -- 3rd Monday in January
IF @nYear>=1993 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Take your Daughter to Work Day',dbo.FloatingDate(4,5,4,@nYear)) -- 4th Thursday in April
IF @nYear>=1908 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Mothers Day',dbo.FloatingDate(2,1,5,@nYear)) -- 2nd Sunday in May
IF @nYear>=1950 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armed Forces Day',dbo.FloatingDate(3,7,5,@nYear)) -- 3rd Saturday in May
IF @nYear>=1910 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Fathers Day',dbo.FloatingDate(2,1,6,@nYear)) -- 2nd Sundy in June
IF @nYear>=1894 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Labor Day',dbo.FloatingDate(1,2,9,@nYear)) -- 1st Monday in September
IF @nYear>=1941 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Thanksgiving',dbo.FloatingDate(4,5,11,@nYear)) -- 4th Thursday in November
---------------------------------------------------------------------------------------
-- Federal holidays that only come every 4 years
---------------------------------------------------------------------------------------
IF (@nYear >= 1937) AND ((@nYear-1937) % 4)=0
BEGIN
SET @WorkDT = CONVERT(SmallDateTime,'01/20/'+CAST(@nYear AS VARCHAR(4))) -- Get Inauguration day
IF DatePart(dw,@WorkDT)=1 SET @WorkDt = DateAdd(d,1,@WorkDt) -- Move to Monday if it falls on Sunday
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
END
IF (@nYear < 1937) AND ((@nYear-1937) % 4)=0
BEGIN
-- Get Inauguration day for years prior to 1937
SET @WorkDT = CONVERT(SmallDateTime,'03/04/'+CAST(@nYear AS VARCHAR(4)))
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
END
---------------------------------------------------------------------------------------
-- Holidays that change based upon year
---------------------------------------------------------------------------------------
IF @nYear >= 1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Presidents Day',dbo.FloatingDate(3,2,2,@nYear)) -- 3rd Monday in February
IF @nYear < 1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Washington''s Birthday',CONVERT(SmallDateTime,'2/22/'+CAST(@nYear AS VARCHAR(4))))
IF (@nYear >=1954)
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Veteran''s Day',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))
IF (@nYear >=1921 and @nYear < 1954)
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armistice',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))
IF @nYear>=1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',dbo.FloatingDate(2,2,10,@nYear)) -- 2nd Monday in October
IF @nYear>=1937 and @nYear <1971
INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',CONVERT(SmallDateTime,'10/12/'+CAST(@nYear AS VARCHAR(4))))
RETURN
END
GO
SELECT * FROM dbo.Holiday_List(2005)
ORDER BY 2
May 21, 2005 at 7:54 pm
The floating date function does not work correctly for 2005 (I didn't check other years)
if you modify the FloatingDate function as follows it will work:
Thanks to Karl Schmitt -Deerfield Bakery http://www.tek-tips.com/faqs.cfm?fid=5075
who also has a calendar generator
ALTER FUNCTION dbo.FloatingDate(@Occur INT,@WeekDay INT,@Month INT,@Year INT)
RETURNS
SMALLDATETIME
AS
BEGIN
DECLARE @Result SMALLDATETIME
DECLARE @StartDate SMALLDATETIME
-- Get Starting date, which is first day of the month
SET @StartDate = CONVERT(SmallDateTime,CAST(@Month AS VARCHAR(2))+'/1/'+CAST(@Year AS VARCHAR(4)))
SET @Result = cast(str(@Month)+'/'+ str((7+ @Weekday-datepart(dw,@StartDate))%7+1) +'/'+
str(@Year) AS datetime)+(@Occur-1)*7
RETURN @Result
END
GO
May you live to be 100 and me 100 but minus a day so I never know that nice people like you have passed away
May 23, 2005 at 8:49 am
In case it's of use, I used this script to build the data dimension in a data warehouse. Bank Holidays are based on UK holidays though. The UDFs dbo.WorkingDay and the functions it uses for Easter & holiday dates are elsewhere on the site (I've posted them before) so you can find them by using Search or looking at my posting history.
IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'DX_Date' AND [type] = 'U')
DROP TABLE dbo.DX_Date
GO
CREATE TABLE dbo.DX_Date
( DateKey int IDENTITY(1,1) NOT NULL,
SQLDate smalldatetime NOT NULL,
DayOfWeek tinyint NOT NULL,
DayOfWeekName varchar(9) NOT NULL,
DayOfMonth tinyint NOT NULL,
DayOfYear smallint NOT NULL,
LastDayOfWeek_B bit NOT NULL,
LastDayOfMonth_B bit NOT NULL,
WeekEndingDate char(8) NOT NULL,
WeekNumber tinyint NOT NULL,
MonthName varchar(9) NOT NULL,
MonthNumber tinyint NOT NULL,
YearMonth char(6) NOT NULL,
Quarter tinyint NOT NULL,
YearQuarter char(6) NOT NULL,
Year smallint NOT NULL,
FinDayOfYear smallint NOT NULL,
FinWeekNumber tinyint NOT NULL,
FinPeriod tinyint NOT NULL,
FinYearPeriod char(6) NOT NULL,
FinQuarter tinyint NOT NULL,
FinYearQuarter char(6) NOT NULL,
FinYear smallint NOT NULL,
BankHoliday_B bit NOT NULL,
Weekday_B bit NOT NULL,
CONSTRAINT DX_Date_PK PRIMARY KEY (DateKey)
)
GO
-- Populate table
CREATE TABLE #numbers
( n int IDENTITY(1,1) PRIMARY KEY CLUSTERED )
WHILE ISNULL(SCOPE_IDENTITY(),0) <= 100000
BEGIN
INSERT #numbers DEFAULT VALUES
END
GO
IF @@DATEFIRST != 7
PRINT 'WARNING: @@DATEFIRST != 7'
DECLARE @dtStartDate datetime, @dtEndDate datetime, @iDays int
SET @dtStartDate = CONVERT(datetime, '19991231')
SET @dtEndDate = CONVERT(datetime, '20101231')
SET @iDays = DATEDIFF(dd, @dtStartDate, @dtEndDate)
INSERT DX_Date (SQLDate, DayOfWeek, DayOfWeekName, DayOfMonth, DayOfYear, LastDayOfWeek_B, LastDayOfMonth_B, WeekEndingDate,
WeekNumber, MonthName, MonthNumber, YearMonth, Quarter, YearQuarter, Year, FinDayOfYear, FinWeekNumber, FinPeriod,
FinYearPeriod, FinQuarter, FinYearQuarter, FinYear, BankHoliday_B, Weekday_B)
SELECTDATEADD(dd, n, @dtStartDate),
DATEPART(dw, DATEADD(dd, n, @dtStartDate)),
DATENAME(dw, DATEADD(dd, n, @dtStartDate)),
DATEPART(dd, DATEADD(dd, n, @dtStartDate)),
DATEPART(dy, DATEADD(dd, n, @dtStartDate)),
CASE WHEN DATEPART(dw, DATEADD(dd, n, @dtStartDate)) = 1 THEN 1 ELSE 0 END,
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) = DATEPART(mm, DATEADD(dd, n + 1, @dtStartDate)) THEN 0 ELSE 1 END,
CONVERT(varchar, DATEADD(dd, n + ((8 - DATEPART(dw, DATEADD(dd, n, @dtStartDate))) % 7), @dtStartDate), 112),
DATEPART(wk, DATEADD(dd, n, @dtStartDate)),
DATENAME(mm, DATEADD(dd, n, @dtStartDate)),
DATEPART(mm, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) +
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) < 10
THEN '0' + CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate)))
ELSE CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate))) END,
DATEPART(qq, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) + '0' + CONVERT(varchar, DATEPART(qq, DATEADD(dd, n, @dtStartDate))),
DATEPART(yy, DATEADD(dd, n, @dtStartDate)),
DATEDIFF(dd, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
DATEDIFF(ww, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
(DATEPART(mm, DATEADD(dd, n, @dtStartDate)) + 8) % 12,
'XXXXXX',
0,
'XXXXXX',
DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 2 THEN 0 ELSE 1 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 1 THEN 0 ELSE 1 END
FROM #numbers
WHERE n <= @iDays
GO
UPDATE DX_Date SET FinPeriod = 12 WHERE FinPeriod = 0
GO
UPDATE DX_Date
SET FinYearPeriod = CONVERT(varchar, FinYear) + CASE WHEN FinPeriod < 10 THEN '0' ELSE '' END + CONVERT(varchar, FinPeriod),
FinQuarter = CASE WHEN FinPeriod IN (1,2,3) THEN 1
WHEN FinPeriod IN (4,5,6) THEN 2
WHEN FinPeriod IN (7,8,9) THEN 3
WHEN FinPeriod IN (10,11,12) THEN 4 END
GO
UPDATE DX_Date
SETFinYearQuarter = CONVERT(varchar, FinYear) + '0' + CONVERT(varchar, FinQuarter)
GO
UPDATE DX_Date
SETFinWeekNumber = 1
WHERE FinWeekNumber = 53
GO
-- Create index
CREATE NONCLUSTERED INDEX DX_Date_IX1
ON DX_Date (SQLDate)
WITH FILLFACTOR = 100
GO
DROP TABLE #numbers
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply