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

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?

---

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

---