|
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 |
|
|