SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Create Date Table with UK & Easter bank holidays
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 05/12/2005 :  17:30:15  Show Profile  Reply with Quote
Also includes ISOWeek & Weekends

Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.

So while looking at Bretts blog and another post on here, i thought i'd post this on here
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698

Special thanks to rockmoose & BOL (as always)

Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend

SET DATEFIRST 1
SET NOCOUNT ON
GO

--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= 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 (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(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 @ISOweek=1
   RETURN(@ISOweek)
END
GO
--END ISOweek

--CREATE Easter algorithm function 
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
	-- Easter date algorithm of Delambre
	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
--END fnDLA_GetEasterdate

--Create the table
CREATE TABLE MyDateTable
(
FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED, 
Period int, 
ISOWeek int, 
WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create

--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
	SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
		INSERT MyDateTable(FullDate, Period, ISOWeek)
		SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom)
	SET @DateFrom = DATEADD(dd,+1,@DateFrom)
END
GO
--End population


/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday
	OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday
GO

UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7)
			GROUP BY DATEPART(yy,FullDate))
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
	OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
			GROUP BY DATEPART(yy,FullDate))
	OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
			GROUP BY DATEPART(yy,FullDate))
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
	OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
			GROUP BY DATEPART(yy,FullDate))
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
	OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
					DATEADD(dd,+2,FullDate) ELSE FullDate END
			FROM MyDateTable 
			WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO

---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
/* End of WorkingDays UPDATE */

--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable

SET NOCOUNT OFF


Andy

Beauty is in the eyes of the beerholder

Edited by - AndyB13 on 05/12/2005 17:40:37

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 05/20/2005 :  03:44:15  Show Profile  Reply with Quote
Andy you're a star I think I may owe you a few beers after this - you like Sam Smiths?


steve

A sarcasm detector, what a great idea.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 05/20/2005 :  14:18:18  Show Profile  Reply with Quote
Great Andy!
Why didn't You tell us You were going to do this ?
You could have gotten some
One of the DBA's implemented a UK calender, (after Your info on the UK holidays some time ago).

rockmoose
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 05/24/2005 :  17:34:21  Show Profile  Reply with Quote
quote:
Originally posted by elwoos

Andy you're a star I think I may owe you a few beers after this - you like Sam Smiths?



Thanks, nah i'm a puff
More of a lager (beer US) man myself - WIFE BEATER (Stella Artois)



quote:
Originally posted by rockmoose

Great Andy!
Why didn't You tell us You were going to do this ?
You could have gotten some
One of the DBA's implemented a UK calender, (after Your info on the UK holidays some time ago).



Thanks rockmoose, i actually did this not long after that post but i forgot to post it on here. It was only when i was reading that other post & Bretts log that i remembered it.


Glad its of some use.
There is probably a more logical/quicker approach to my example but given that the script would only need to be run once (30 seconds/100 years of dates) I didnt really see the point.

Andy

Beauty is in the eyes of the beerholder

Edited by - AndyB13 on 05/24/2005 17:40:53
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 12/05/2006 :  10:34:43  Show Profile  Reply with Quote
Now that the beer barrel is empty and the celebrations are over...

So as far as I can see, this deals with:
New Year's Day
New Year's Day Holiday
Good Friday
Easter Sunday
Easter Monday
Early May Bank Holiday
Christmas Day
Boxing Day

What about:
Early May Bank Holiday
Spring Bank Holiday
Summer Bank Holiday
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000