HeavenCore | IT Solutions Built on Open Source Software
View Thread
 Print Thread
Create Working Days table with bank holidays etc
admin
This script is genius, not sure who the original author was, but it does mention sqlteam.com etc

Download source  Code
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
 
Jump to Forum: