-
Function which returns holidays of the year
Here I am attaching code of functiuon which returns the holidays of the given year
CREATE FUNCTION Fn_Holiday_Of_Year(@in_CurrentYear CHAR(4) )
RETURNS @Y_Holidays TABLE
(
Dt SMALLDATETIME
)
AS
/************************************************** ************************************
Function NAME: Fn_Holiday_Of_Year
CALLED FROM: ----
PURPOSE: Returns the bank holidays of the given year( 11 Days )
TABLES USED AND ACTIONS DONE
INPUT : YEAR 'YYYYY'
OUTPUT : List of the holidays
MODIFICATION HISTORY
Person Date Comments
Vinod Patel Dec 19, 2008 Created the function
************************************************** **********************************/
BEGIN
DECLARE
@Holidays TABLE
(
Dt SMALLDATETIME
)
DECLARE
@NewYear AS DATETIME,
@Canada_Day AS DATETIME,
@Remembrance_Day AS DATETIME,
@Christmas_Day AS DATETIME,
@Boxing_Day AS DATETIME,
@Family_Day AS DATETIME,
@Civic_Day AS DATETIME,
@Labour_Day AS DATETIME,
@Thanksgiving_Day AS DATETIME,
@Victoria_Day AS DATETIME,
@Good_Friday AS DATETIME,
--Variable to calculate Easter
@Year char(4),
@c INT
, @n INT
, @k INT
, @i INT
, @j INT
, @l INT
, @m INT
, @d INT
, @Easter DATETIME
--================================================== ====================================
--STATIC HOLYDAY : New Year,Canada Day,Remembrance Day,Christmas Day and Boxing Day
--================================================== ====================================
--New Year
SET @NewYear = @in_CurrentYear+'-01-01'
--IF New Year’s Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@NewYear)='Saturday'
SET @NewYear = @NewYear- 1
IF DATENAME(dw,@NewYear)='Sunday'
SET @NewYear = @NewYear + 1
INSERT INTO @Holidays
VALUES( @NewYear)
--Canada Day
SET @Canada_Day = @in_CurrentYear+'-07-01'
--IF Canada Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@Canada_Day)='Saturday'
SET @Canada_Day = @Canada_Day- 1
IF DATENAME(dw,@Canada_Day)='Sunday'
SET @Canada_Day = @Canada_Day + 1
INSERT INTO @Holidays
VALUES( @Canada_Day)
--Remembrance Day
SET @Remembrance_Day = @in_CurrentYear+'-11-11'
--IF Remembrance Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@Remembrance_Day)='Saturday'
SET @Remembrance_Day = @Remembrance_Day- 1
IF DATENAME(dw,@Remembrance_Day)='Sunday'
SET @Remembrance_Day = @Remembrance_Day + 1
INSERT INTO @Holidays
VALUES( @Remembrance_Day)
--Christmas Day
SET @Christmas_Day = @in_CurrentYear+'-12-25'
--IF Christmas Day falls on a Saturday or Sunday the holiday wil be on the working day immediately before or after the New Year’s Day.
IF DATENAME(dw,@Christmas_Day)='Saturday'
SET @Christmas_Day = @Christmas_Day- 1
IF DATENAME(dw,@Christmas_Day)='Sunday'
SET @Christmas_Day = @Christmas_Day + 1
INSERT INTO @Holidays
VALUES( @Christmas_Day)
--Boxing Day
SET @Boxing_Day = @in_CurrentYear+'-12-26'
--The holiday will be the working day immediately after the Christmas Day holiday.
IF DATENAME(dw,@Christmas_Day)='Friday'
SET @Boxing_Day = @Christmas_Day+ 3
IF DATENAME(dw,@Christmas_Day)='Monday'
SET @Boxing_Day = @Christmas_Day + 1
INSERT INTO @Holidays
VALUES( @Boxing_Day)
--================================================== ====================================
--DYNAMIC HOLYDAY : These holidays occur on a specific day(MONDAY) of week in a specific week and month.
--================================================== ====================================
--Family Day is a statutory holiday that takes place on the third Monday of February
SET @Family_Day = @in_CurrentYear+'-02-01'
IF DATENAME(dw,@Family_Day)='Sunday'
SET @Family_Day = @Family_Day + 15
ELSE IF DATENAME(dw,@Family_Day)='Monday'
SET @Family_Day = @Family_Day + 14
ELSE IF DATENAME(dw,@Family_Day)='Tuesday'
SET @Family_Day = @Family_Day + 20
ELSE IF DATENAME(dw,@Family_Day)='Wednesday'
SET @Family_Day = @Family_Day + 19
ELSE IF DATENAME(dw,@Family_Day)='Thursday'
SET @Family_Day = @Family_Day + 18
ELSE IF DATENAME(dw,@Family_Day)='Friday'
SET @Family_Day = @Family_Day + 17
ELSE IF DATENAME(dw,@Family_Day)='Saturday'
SET @Family_Day = @Family_Day + 16
INSERT INTO @Holidays
VALUES( @Family_Day)
--Civic Holiday is on the first Monday of August
SET @Civic_Day = @in_CurrentYear+'-08-01'
IF DATENAME(dw,@Civic_Day)='Sunday'
SET @Civic_Day = @Civic_Day + 1
ELSE IF DATENAME(dw,@Civic_Day)='Monday'
SET @Civic_Day = @Civic_Day + 0
ELSE IF DATENAME(dw,@Civic_Day)='Tuesday'
SET @Civic_Day = @Civic_Day + 6
ELSE IF DATENAME(dw,@Civic_Day)='Wednesday'
SET @Civic_Day = @Civic_Day + 5
ELSE IF DATENAME(dw,@Civic_Day)='Thursday'
SET @Civic_Day = @Civic_Day + 4
ELSE IF DATENAME(dw,@Civic_Day)='Friday'
SET @Civic_Day = @Civic_Day + 3
ELSE IF DATENAME(dw,@Civic_Day)='Saturday'
SET @Civic_Day = @Civic_Day + 2
INSERT INTO @Holidays
VALUES( @Civic_Day)
-- Labour Day (First Monday in September)
SET @Labour_Day = @in_CurrentYear+'-09-01'
IF DATENAME(dw,@Labour_Day)='Sunday'
SET @Labour_Day = @Labour_Day + 1
ELSE IF DATENAME(dw,@Labour_Day)='Monday'
SET @Labour_Day = @Labour_Day + 0
ELSE IF DATENAME(dw,@Labour_Day)='Tuesday'
SET @Labour_Day = @Labour_Day + 6
ELSE IF DATENAME(dw,@Labour_Day)='Wednesday'
SET @Labour_Day = @Labour_Day + 5
ELSE IF DATENAME(dw,@Labour_Day)='Thursday'
SET @Labour_Day = @Labour_Day + 4
ELSE IF DATENAME(dw,@Labour_Day)='Friday'
SET @Labour_Day = @Labour_Day + 3
ELSE IF DATENAME(dw,@Labour_Day)='Saturday'
SET @Labour_Day = @Labour_Day + 2
INSERT INTO @Holidays
VALUES( @Labour_Day)
-- Thanks giving Day (Second Monday in October)
SET @Thanksgiving_Day = @in_CurrentYear+'-10-01'
IF DATENAME(dw,@Thanksgiving_Day)='Sunday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 8
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Monday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 7
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Tuesday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 13
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Wednesday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 12
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Thursday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 11
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Friday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 10
ELSE IF DATENAME(dw,@Thanksgiving_Day)='Saturday'
SET @Thanksgiving_Day = @Thanksgiving_Day + 9
INSERT INTO @Holidays
VALUES( @Thanksgiving_Day)
--Victoria Day (Monday on or before May 24)
SET @Victoria_Day = @in_CurrentYear+'-05-24'
IF DATENAME(dw,@Victoria_Day)='Sunday'
SET @Victoria_Day = @Victoria_Day - 6
ELSE IF DATENAME(dw,@Victoria_Day)='Monday'
SET @Victoria_Day = @Victoria_Day - 0
ELSE IF DATENAME(dw,@Victoria_Day)='Tuesday'
SET @Victoria_Day = @Victoria_Day - 1
ELSE IF DATENAME(dw,@Victoria_Day)='Wednesday'
SET @Victoria_Day = @Victoria_Day - 2
ELSE IF DATENAME(dw,@Victoria_Day)='Thursday'
SET @Victoria_Day = @Victoria_Day - 3
ELSE IF DATENAME(dw,@Victoria_Day)='Friday'
SET @Victoria_Day = @Victoria_Day - 4
ELSE IF DATENAME(dw,@Victoria_Day)='Saturday'
SET @Victoria_Day = @Victoria_Day - 5
INSERT INTO @Holidays
VALUES( @Victoria_Day)
--Good Friday (Friday before Easter Sunday)
-- Ester Day Calculation
SET @Year = @in_CurrentYear
SET @c = (@Year / 100)
SET @n = @Year - 19 * (@Year / 19)
SET @k = (@c - 17) / 25
SET @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15
SET @i = @i - 30 * ( @i / 30 )
SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
SET @j = @j - 7 * (@j / 7)
SET @l = @i - @j
SET @m = 3 + (@l + 40) / 44
SET @d = @l + 28 - 31 * ( @m / 4 )
SET @Easter = (SELECT RIGHT('0' + CONVERT(VARCHAR(2),@m),2) + '/' + RIGHT('0' + CONVERT(VARCHAR(2),@d),2) + '/' + CONVERT(CHAR(4),@Year))
SET @Good_Friday = @Easter -2
INSERT INTO @Holidays
VALUES( @Good_Friday)
INSERT INTO @Y_Holidays
SELECT DT FROM @Holidays ORDER BY dt
RETURN
END
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|