Forums: Create Holidays Table - Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Create Holidays Table SQL Server 2005

#1 User is offline   Dale 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 91
  • Joined: 05-January 06
  • Gender:Male
  • Location:United Kingdom

Posted 24 December 2007 - 11:31 AM

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

Best Regards
Dale
0

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users