How do I make a proc that calculates Federal Holidays?

[adam0101]

I need to make a stored procedure that will return a list of Federal Holidays for a given year. How should I do this? Here are the holidays:

Static Holidays:
New Year's Day                 January 1
Independence Day               July 4
Veterans Day                   November 11
Christmas Day                  December 25

Calculated Holidays:
Martin Luther King's Birthday  Third Monday in January
Washington's Birthday          Third Monday in February
Memorial Day                   Last Monday in May
Labor Day                      First Monday in September
Columbus Day                   Second Monday in October
Thanksgiving                   Fourth Thursday in November

Thanks in advance,
Adam

 

[rac2]

I would make a couple of tables
HolidaysStatic (name,month,day_of_month)
HolidaysToCalculate(name, month, day_of_week, week)

The columns are VARCHAR except day_of_week and week are INT.
Call the stored procedure with a parameter equal to the year. EXECUTE proc_get_federal_holidays '2002'

A query for the static holidays seems pretty straightforward.
SELECT name,
CONVERT(DATETIME, month+'/'+day_of_month+'/'@year) AS date_of_holiday
FROM HolidaysStatic

Then UNION that with a query on a temporary table that has been filled with the calculated holidays.

SELECT name, date_of_holiday FROM #HolidayCalculated

So then the only difficult part is calculating the dates of the holidays.

For this I would define a cursor so that I can loop through the rows in HolidaysToCalculate. This will give me variables @name, @month, @day_of_week, and @week.

Then I would loop through the first seven days of the month to find the date of the first day_of_week using the DATEPART function. Then I would add the appropriate number of days to that date to calculate the date of the holiday.

@date_of_holiday = DATEADD( (day, (@week-1))*7, @first_day_of_week )

Then INSERT the holiday name and date into the temporary table. And repeat for the next holiday.

Should you do this? No, I think you should hire someone to do this for you. ;-)

 

[r937]

sweet

i would run the "holidays to calculate" for several years into the future, and simply store the results into HolidaysStatic (assuming you add year as a column)

then adam wouldn't need a stored proc, he can simply query the single table

rudy

http://rudy.ca/

 

[carp]

I would also go with the stored proc and set it up to run every 1 Jan. Have it calculate the holidays for three (or however many) years in the future. That is, go ahead and populate your table for 2002, 2003, 2004. On 1 Jan 2003, the proc wakes up and calculates the holidays for 2005.

 

[adam0101]

Thanks for all the fast replies. I think I might just put the dates for a couple years in a table as suggested. But in case I need to calculate holidays for any year, I went ahead and used a few ideas given to write the code below. Comments and suggestions are welcome.

DECLARE @year varchar(4)
SET @year = '2002'

DECLARE @incDate datetime
DECLARE @martinLuther datetime
DECLARE @washington datetime
DECLARE @memorial datetime
DECLARE @labor datetime
DECLARE @columbus datetime
DECLARE @thanksgiving datetime

SET @incDate='1/1/'+@year
WHILE DatePart(dw,@incDate)<>2
  SET @incDate=DateAdd(d,1,@incDate)
SET @martinLuther=DateAdd(ww,2,@incDate)

SET @incDate='2/1/'+@year
WHILE DatePart(dw,@incDate)<>2
  SET @incDate=DateAdd(d,1,@incDate)
SET @washington=DateAdd(ww,2,@incDate)

SET @incDate=DateAdd(d,-1,'6/1/'+@year)
WHILE DatePart(dw,@incDate)<>2
  SET @incDate=DateAdd(d,-1,@incDate)
SET @memorial=@incDate

SET @incDate='9/1/'+@year
WHILE DatePart(dw,@incDate)<>2
  SET @incDate=DateAdd(d,1,@incDate)
SET @labor=@incDate

SET @incDate='10/1/'+@year
WHILE DatePart(dw,@incDate)<>2
  SET @incDate=DateAdd(d,1,@incDate)
SET @columbus=DateAdd(ww,1,@incDate)

SET @incDate='11/1/'+@year
WHILE DatePart(dw,@incDate)<>2
  SET @incDate=DateAdd(d,1,@incDate)
SET @thanksgiving=DateAdd(ww,3,@incDate)

SELECT CONVERT(DATETIME, '1/1/'+@year) AS Holiday
UNION
SELECT CONVERT(DATETIME, '7/4/'+@year) 
UNION
SELECT CONVERT(DATETIME, '11/11/'+@year) 
UNION
SELECT CONVERT(DATETIME, '12/25/'+@year) 
UNION
SELECT @martinLuther
UNION
SELECT @washington
UNION
SELECT @memorial
UNION
SELECT @labor
UNION
SELECT @columbus
UNION
SELECT @thanksgiving
ORDER BY 1

 

[adam0101]

Oops, I caught a mistake. The last &quot;While&quot; statement should read WHILE DatePart(dw,@incDate)<>5