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