DateAdd - Working Days Only

  • I have a calendar table like this:

    SmallDateDayNameDayDescription

    04/11/2010ThursdayWorking Day

    05/11/2010FridayWorking Day

    06/11/2010SaturdayWeekend

    07/11/2010SundayWeekend

    08/11/2010MondayWorking Day

    09/11/2010TuesdayWorking Day

    I would like to use maybe the DateAdd function to calculate 3 working days after a particular date.

    Eg 04/11/2010 should return 09/11/2010. ie. it should not include "Weekend" in the calculation.

    Any advice on this would be very welcome.

    Thankyou.

  • Please paste some DDL code and sample data.

    In theory, I would have the working day column as a bit data type. if 0 non working day, if 1 working day.

    on your select query retreive the row_Number() of the date where your working day column is set to 1.

    return your row_number for the currentdate + 3.

  • Hi, Thankyou for your reply.

    I have changed the calendar table so that it now has a BIT field for IsWorkingDay.

    I dont really have any code that would make sense to show you (other than below).

    When you say i should get the RowNumber, do you mean as in the RANK() function?

    So far i have this:

    SELECT RANK() OVER (ORDER BY [RowNumber]) as RowNum, SmallDate

    from ltbl_Calendar_Working_Days

    WHERE SmallDate = '2010-11-04' AND isWorkingDay = 1

    order by RowNum Which from the above data extract just returns:

    RowNumSmallDate

    12010-11-04

    I need to create a function that passes in the date (say) 2010-11-04 and then returns the date 2010-11-09.

    The data below is derived from:

    SELECT RANK() OVER (ORDER BY [RowNumber]) as RowNum, SmallDate

    from ltbl_Calendar_Working_Days

    WHERE SmallDate BEWTWEEN '2010-11-02' AND '2010-11-12' AND isWorkingDay = 1

    order by RowNum

    RowNumSmallDate

    12010-11-02

    22010-11-03

    32010-11-04

    42010-11-05

    52010-11-08

    62010-11-09

    72010-11-10

    82010-11-11

    92010-11-12

    The bit i'm struggling with is adding the 3 working days that i need

  • If you are concerned about optimizing query performance and/or keeping your working day queries simple, you could amend your calendar table to include an additional indexed column that increments by 1 on each working day. Something like the following might work for you:

    CREATE TABLE MyCalendar (

    SmallDate smalldatetime NOT NULL PRIMARY KEY,

    IsWorkingDay bit NOT NULL,

    WorkingDayNumber int NOT NULL

    /* other columns */

    )

    SmallDate DayName DayDescription IsWorkingDay WorkingDayNumber

    04/11/2010 Thursday Working Day 1 145

    05/11/2010 Friday Working Day 1 146

    06/11/2010 Saturday Weekend 0 146

    07/11/2010 Sunday Weekend 0 146

    08/11/2010 Monday Working Day 1 147

    09/11/2010 Tuesday Working Day 1 148

    The absolute value of the WorkingDayNumber column is not particularly important, only the difference between the column values for pairs of rows. With this column available, your query is now quite straightforward.

    SELECT C.SmallDate

    FROM MyCalendar C

    JOIN MyCalendar CREF ON (C.WorkingDayNumber = CREF.WorkingDayNumber + 3)

    WHERE (CREF.SmallDate = @ReferenceDate)

    AND (C.IsWorkingDay = 1)

  • Thankyou Andrew,

    that makes perfect sense...and so very simple (when you know how!)

    I don't know though how to populate the new field with an incremented value.

    Can you help with this or should i really be looking elsewhere?

  • the Code i was asking for is the create statements and insert dummy data, created below

    this should resolve your problem. let me know how it goes

    ---Create Table

    create table test( smalldate datetime, [DayName] nvarchar(50),WorkingDay bit, RowNum int)

    ---Insert Values

    insert into test(smalldate, DayName, WorkingDay)

    select '2010/11/04',' Thursday', 1

    insert into test(smalldate, DayName, WorkingDay)

    select '2010/11/05' ,'Friday',1

    insert into test(smalldate, DayName, WorkingDay)

    select '2010/11/06' ,'Saturday', 0

    insert into test(smalldate, DayName, WorkingDay)

    select '2010/11/07' ,'Sunday', 0

    insert into test(smalldate, DayName, WorkingDay)

    select '2010/11/08' ,'Monday', 1

    insert into test(smalldate, DayName, WorkingDay)

    select '2010/11/09', 'Tuesday', 1

    --test select

    select * from test

    ---Update with Row Number

    update test

    set RowNum=b.RowNum

    from (SELECT RANK() OVER (ORDER BY [smalldate]) as RowNum, SmallDate

    from test

    WHERE WorkingDay = 1 )b

    where test.smalldate=b.smalldate

    update test

    set RowNum=0

    where RowNum is null

    -- You can probably create a stored proc to accept a date on this query

    declare @day datetime

    set @day = '2010-11-04 00:00:00.000'

    select *

    from test

    where rownum in (

    select RowNum + 3

    from test

    where smalldate=@day)

  • ah sorry. Here's the code i used to create the initial calendar table.

    Its 2 functions and then an SP. Most of the code i found on this forum and just added bits to it.

    Its very useful..

    Function for getting the week number:

    CREATE FUNCTION fcnWeekNumber (@DATE datetime)

    RETURNS int

    AS

    BEGIN

    DECLARE @WeekNumber int

    SET @WeekNumber= 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 (@WeekNumber=0)

    SET @WeekNumber=dbo.fcnWeekNumber(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 @WeekNumber=1

    RETURN(@WeekNumber)

    END

    GO

    Function for getting Easter dates:

    CREATE FUNCTION fcnGetEasterdate(@year INT)

    RETURNS CHAR (8)

    AS

    BEGIN

    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-2 = @YEAR / 100

    SET @C = @YEAR%100

    SET @D = @b-2 / 4

    SET @E = @b-2%4

    SET @F = (@B + 8) / 25

    SET @G = (@B - @F + 1) / 3

    SET @h = ( 19 * @a + @b-2 - @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

    SP to create caledar table:

    SET DATEFIRST 1

    SET NOCOUNT ON

    GO

    CREATE TABLE tblCalendarWorkingDays

    (

    RowNumber int IDENTITY (1, 1),

    FullDate datetime,

    SmallDate DATE,

    Period INTEGER,

    DayDescription varchar(50) CONSTRAINT DF_tblCalendarWorkingDays_WorkDay DEFAULT 'Working Day',

    IsWorkingDay BIT CONSTRAINT DF_tblCalendarWorkingDays_IsWorkingDay DEFAULT 0,

    DayOfYear INTEGER,

    DayNo INTEGER,

    DayName varchar(20),

    WeekNo INTEGER,

    MonthNo INTEGER,

    MonthName varchar(20),

    QuarterNo INTEGER,

    YearNo INTEGER

    )

    GO

    --Populate table with required dates

    DECLARE @DateFrom datetime, @DateTo datetime, @Period int

    SET @DateFrom = CONVERT(datetime,'20100101') --yyyymmdd (1st Jan 2010) amend as required

    SET @DateTo = CONVERT(datetime,'20201231') --yyyymmdd (31st Dec 2020) amend as required

    WHILE @DateFrom <= @DateTo

    BEGIN

    SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))

    INSERT tblCalendarWorkingDays

    (FullDate, SmallDate, Period, DayOfYear,DayNo,DayName,WeekNo,MonthNo,MonthName,QuarterNo,YearNo)

    SELECT @DateFrom,@DateFrom, @Period,

    DATENAME(DayOfYear,@DateFrom), -- DayOfYear

    DATENAME(day,@DateFrom), -- DayNo

    DATENAME(weekday,@DateFrom), -- DayName

    dbo.fcnWeekNumber(@DateFrom), --WeekNumber

    DATEPART(month,@DateFrom), -- MonthNo

    DATENAME(month,@DateFrom), -- MonthName

    DATENAME(Quarter,@DateFrom), -- QuarterNo

    DATENAME(year,@DateFrom) -- YearNo

    SET @DateFrom = DATEADD(dd,+1,@DateFrom)

    END

    GO

    --Start of DayDescriptions UPDATE

    ---------------------------------------WEEKENDS--------------------------------------

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'Weekend'

    WHERE DATEPART(dw,FullDate) IN (6,7)

    GO

    --------------------------------EASTER---------------------------------------------

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'Good Friday'

    WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fcnGetEasterdate(DATEPART(yy,FullDate))))

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'Easter Monday'

    WHERE FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fcnGetEasterdate(DATEPART(yy,FullDate))))

    GO

    --------------------------------NEW YEAR-------------------------------------------

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'New Year Bank Holiday'

    WHERE FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN

    DATEADD(dd,+2,FullDate) ELSE FullDate END

    FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dd,FullDate) IN (1))

    GO

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'New Years Eve'

    FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (31)

    GO

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'New Years Day'

    FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dd,FullDate) IN (1)

    GO

    ---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'May Bank Holiday'

    WHERE FullDate IN

    (SELECT MIN(FullDate) FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1

    GROUP BY DATEPART(yy,FullDate))

    OR FullDate IN

    (SELECT MAX(FullDate) FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1

    GROUP BY DATEPART(yy,FullDate))

    GO

    --------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'August Bank Holiday'

    WHERE FullDate IN

    (SELECT MAX(FullDate) FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1

    GROUP BY DATEPART(yy,FullDate))

    GO

    --------------------XMAS(Create Holiday on next working day if on Sat/Sun)--------------------

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'Christmas Bank Holiday'

    WHERE FullDate IN

    (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN

    DATEADD(dd,+2,FullDate) ELSE FullDate END

    FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))

    GO

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'Christmas Eve'

    FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (24)

    GO

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'Christmas Day'

    FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25)

    GO

    UPDATE tblCalendarWorkingDays

    SET DayDescription = 'Boxing Day'

    FROM tblCalendarWorkingDays

    WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (26)

    GO

    --------------------Set working day flag------------------------------

    UPDATE tblCalendarWorkingDays

    SET IsWorkingDay = 1

    WHERE DayDescription = 'Working Day'

    GO

    SET NOCOUNT OFF

    I'd now like to add another field which will increment the working day only value like what Andrew mentioned in his last post. I think this table will then be very handy for allsorts of date type queries

  • Howdy EML,

    Did the solution provide resolution?

    (sound like a rapper :-))

  • Hi Shanu,

    I'm really sorry i did not reply to your message straight away.

    I did get it all working in the end. Very similar to the code above.

    If you need the full set of code i will be more than happy to post if you let me know.

    Thanks for all you help 🙂

  • EML (1/12/2011)


    Hi Shanu,

    I'm really sorry i did not reply to your message straight away.

    I did get it all working in the end. Very similar to the code above.

    If you need the full set of code i will be more than happy to post if you let me know.

    Thanks for all you help 🙂

    Pleasure,

    Probably would be a good idea to post your solution as a reference for anyone else having a similar issue.

    cheers,


  • SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    --      Author: |:-)
    -- Create date: 2018-02-22
    -- Last update: 2018-02-22
    -- Description: Version 1, calculates date from current date
    --
    -- =============================================    
    --Example: EXEC [stored procedure] [days], [weeks], [months]  --you must supply a number other than '0' in at least one field
    --EXEC sp_calculate_date 1, 0, 0
    --drop procedure sp_calculate_date
    CREATE PROCEDURE [dbo].[sp_calculate_date]
    (
        @pi_days INT,
        @pi_weeks INT,
        @pi_months INT    
    )
    AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
    DECLARE @pi_count INT,
        @date DATE,
        @new_date DATE

    SET @date = CONVERT(DATE, GETDATE())
    SET @pi_days = ((@pi_days) + (@pi_weeks * 7) + (DATEDIFF(DAY, GETDATE(), (DATEADD(MONTH, @pi_months, CONVERT(DATE, @date))))))
    SET @pi_count = 1

    WHILE (@pi_days <> 0)
    BEGIN
        SET @new_date = (DATEADD(DAY, @pi_count, CONVERT(DATE, @date)))
        
        IF (DATEPART(dw, @new_date) in (1, 7))
        BEGIN
            SET @pi_count = @pi_count + 1      
        END

        ELSE
        BEGIN
            SET @pi_count = @pi_count + 1
            SET @pi_days = @pi_days - 1
        END
    END

    SELECT @new_date

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply