• Hi, J Gravelle

    There is a data warehousing structure that will make it much easier to do this calc, as well as the flexibility to do loads more...The Date dimension

    By using a date dimension, you won't need to loop, and can use one set-based statement to get the total.

    Just some info on a date dimension - essentially, a date dimension is a table that allows you to set certain metadata around a date. You can for instance indicate whether a date is a week day, holiday, etc.

    An example of a date dimension is:

    Create table DimDate

    (

    DateIDint Primary Key

    -- Generally the format yyyymmdd, e.g. 20081201 for 1 Dec 2008

    ,DateStampDateTime

    ,Year_IDint

    ,Month_Namevarchar(200)

    ,Month_IDint

    ,Day_IDint

    ,IsWeekDaybit

    ,IsHolidaybit

    )

    go

    -- Populate the table using a MDM / SQL tool / script

    Then populate the table with all the dates between a specific range e.g. 1 Jan 2000 to 31 Dec 2020

    You can now at a glance see if a date is a holiday or a work day.

    Now, if you want to calc the date of "x" working days forward, you can write a simple function like the one below (there are probably LOADS of better ways than what I did below, but this was the first way that came to mind).

    Cheers.

    Zanoni Labuschagne

    -- Function Code

    creaet function fn_CalcDate

    (

    @StartDatedatetime

    ,@intDaysint

    )

    Returns DateTime

    as

    begin

    Declare@ReturnDatedatetime

    ,@intRowsint

    declare@DateRangetable

    (

    RowIDint identity(1,1)

    ,DateStampdatetime

    )

    Insert into @DateRange

    (

    DateStamp

    )

    SelectDateStamp

    fromDimDate

    whereisWeekday= 1

    andisHoliday= 0

    Order byDateStamp

    Select@intRows= max(RowID)

    from@DateRange

    if @intDays <= @intRows

    -- if the # of days in the table var is more than the days used to calc, find the relevant row

    begin

    Select@ReturnDate= DateStamp

    from@DateRange

    Where@intRows= RowID

    end

    else

    -- if the days in the table is less, use the last

    begin

    Select@ReturnDate= max(DateStamp)

    from@DateRange

    end

    Return@ReturnDate

    end


    _____________________________________________________________________
    Select 'Remember, we were all newbies once'
    From Truth
    Order by Experience

    _____________________________________________________________________