Posts: 630
Joined: 19/11/2006 00:08
|
This script is genius, not sure who the original author was, but it does mention sqlteam.com etc
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
Jordon Pilling | Heavencore Administrator
|
