This topic is locked

Create Holidays Table

12/24/2007 6:31:25 AM
Calendar Template tips and tricks
D
dlangham author

Not sure if this SQL Script is of use to anyone with the Calendar Template or not as I haven't yet implemented it.

This is a SQL Server Script that will create a table with the following:
All Days as per settings (Currently 1st Jan 2008 to 31st Dec 2020)

Easter Holidays

Bank Holidays

Christmas Holidays

New Year Holidays

Weekends and Work Days
These are based on UK Holidays but the script could easilly be changed to reflect any countries holidays.

SET DATEFIRST 1

SET NOCOUNT ON

GO
--Create Week Number Function

CREATE FUNCTION WeekNumber (@DATE datetime)

RETURNS int

AS

BEGIN

DECLARE @WeekNumber int

SET @WeekNumber= DATEPART(wk,@DATE)+1

-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')

--Special cases: Jan 1-3 may belong to the previous year

IF (@WeekNumber=0)

SET @WeekNumber=dbo.WeekNumber(CAST(DATEPART(yy,@DATE)-1

AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1

--Special case: Dec 29-31 may belong to the next year

IF ((DATEPART(mm,@DATE)=12) AND

((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))

SET @WeekNumber=1

RETURN(@WeekNumber)

END

GO
--CREATE Easter Date function

CREATE FUNCTION fnDLA_GetEasterdate(@year INT)

RETURNS CHAR (8)

AS

BEGIN

DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,

@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT



SET @A = @YEAR%19

SET @B = @YEAR / 100

SET @C = @YEAR%100

SET @D = @B / 4

SET @E = @B%4

SET @F = (@B + 8) / 25

SET @G = (@B - @F + 1) / 3

SET @H = ( 19 * @A + @B - @D - @G + 15)%30

SET @I = @C / 4

SET @K = @C%4

SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7

SET @M = (@A + 11 * @H + 22 * @L) / 451

SET @O = 22 + @H + @L - 7 * @M
IF @O > 31

BEGIN

SET @R = @O - 31 + 400 + @YEAR * 10000

END

ELSE

BEGIN

SET @R = @O + 300 + @YEAR * 10000

END

RETURN @R

END

GO
--Create Holiday_Dates table

CREATE TABLE Holiday_Dates

(

FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED,

Period int,

WeekNumber int,

DayDescription varchar(50) CONSTRAINT DF_Holiday_Dates_WorkDay DEFAULT 'Working Day'

)

GO
--Populate table with required dates

DECLARE @DateFrom datetime, @DateTo datetime, @Period int

SET @DateFrom = CONVERT(datetime,'20080101') --yyyymmdd (1st Jan 2008) amend as required

SET @DateTo = CONVERT(datetime,'20201231') --yyyymmdd (31st Dec 2020) amend as required

WHILE @DateFrom <= @DateTo

BEGIN

SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))

INSERT Holiday_Dates(FullDate, Period, WeekNumber)

SELECT @DateFrom, @Period, dbo.WeekNumber(@DateFrom)

SET @DateFrom = DATEADD(dd,+1,@DateFrom)

END

GO
--Start of DayDescriptions UPDATE

---------------------------------------WEEKENDS--------------------------------------

UPDATE Holiday_Dates

SET DayDescription = 'Weekend'

WHERE DATEPART(dw,FullDate) IN (6,7)

GO

--------------------------------EASTER---------------------------------------------

UPDATE Holiday_Dates

SET DayDescription = 'Good Friday'

WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate))))

UPDATE Holiday_Dates

SET DayDescription = 'Easter Monday'

WHERE FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate))))

GO

--------------------------------NEW YEAR-------------------------------------------

UPDATE Holiday_Dates

SET DayDescription = 'New Year Bank Holiday'

WHERE FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN

DATEADD(dd,+2,FullDate) ELSE FullDate END

FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dd,FullDate) IN (1))

GO

UPDATE Holiday_Dates

SET DayDescription = 'New Years Eve'
FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (31)

GO

UPDATE Holiday_Dates

SET DayDescription = 'New Years Day'
FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dd,FullDate) IN (1)

GO

---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------

UPDATE Holiday_Dates

SET DayDescription = 'May Bank Holiday'

WHERE FullDate IN (SELECT MIN(FullDate) FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1

GROUP BY DATEPART(yy,FullDate))

OR FullDate IN (SELECT MAX(FullDate) FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1

GROUP BY DATEPART(yy,FullDate))

GO

--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------

UPDATE Holiday_Dates

SET DayDescription = 'August Bank Holiday'

WHERE FullDate IN (SELECT MAX(FullDate) FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1

GROUP BY DATEPART(yy,FullDate))

GO

--------------------XMAS(Create Holiday on next working day if on Sat/Sun)--------------------

UPDATE Holiday_Dates

SET DayDescription = 'Christmas Bank Holiday'

WHERE FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN

DATEADD(dd,+2,FullDate) ELSE FullDate END

FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))

GO

UPDATE Holiday_Dates

SET DayDescription = 'Christmas Eve'
FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (24)

GO

UPDATE Holiday_Dates

SET DayDescription = 'Christmas Day'
FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25)

GO

UPDATE Holiday_Dates

SET DayDescription = 'Boxing Day'
FROM Holiday_Dates

WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (26)

GO

--------------------Clean Up--------------------

DROP FUNCTION fnDLA_GetEasterdate

DROP FUNCTION WeekNumber
SET NOCOUNT OFF