20 day count

  • Apologies if this has been discussed before:

    I need to calculate a due date from a given start date to exclude weekends and holidays.

    Can anyone help?

    Many thanks

  • Hi Duncan,

    you first need to decide which days are weekend by using the datepart function and create a list of every day between the 2 stating which is a weekend. then sum the difference.

    Excluding Holidays is more involved as you have to individually update the table to show which days have been holidays which SQL doesn't store.an example using the code below could be:

    update #temp

    set workdayind = 0

    where fulldate = 'Date of holiday'

    but you'd need one per holiday.

    set dateformat ymd

    set datefirst 1

    declare @StartDate datetime

    declare @endDate datetime

    set @StartDate = cast('2014/01/31' as datetime) ---- replace with startdate

    set @EndDate = DATEADD(dd, -1, left(getdate(), 11)) ----- replace with endDate

    create table #temp

    (Fulldate datetime, WorkingDayind tinyint)

    Repeat:

    set @Startdate = @StartDate + 1

    insert into #temp

    (Fulldate, WorkingDayind)

    select @StartDate,

    case when datepart(dw, @StartDate) <= 5 then 1 else 0 end

    if @StartDate < @endDate goto Repeat

    select SUM(WorkingDayind) from #temp

    select * from #temp

    drop table #temp

    I'm more than open to a simpler way of doing it but this was the only way I could think of at the time.

    J

  • try this

    Declare @start_dte as datetime = '01-Apr-2014'

    , @end_dte as datetime = '30-Apr-2014'

    Declare @WeekDayTable Table ( colWeekDayID tinyint

    , ColWeekDayName varchar(20)

    , ColIsWeekEnd bit

    )

    Declare @HolidayTable Table ( ID int identity(1,1)

    , HolidayDt Datetime

    )

    insert into @WeekDayTable

    Select 1,'Sunday', 0 union all

    Select 2,'Monday', 1union all

    Select 3,'Tuesday', 1union all

    Select 4,'Wednesday', 1 union all

    Select 5,'Thursday', 1union all

    Select 6,'Friday', 1union all

    Select 7,'Saturday', 0;

    insert into @HolidayTable

    Select '02-Apr-2014';

    ;With cCalender

    AS

    (

    Select N AS ID, dateadd(dd,N-1,@start_dte) AS CalenderDate

    , DATEPART(DW,dateadd(dd,N-1,@start_dte)) as WeekDays

    , DATENAME(DW,dateadd(dd,N-1,@start_dte)) as WeekDayName

    from

    (

    Select top 10000 row_number() Over (Order by o.id) as N

    from sysObjects o

    cross join sysobjects oo

    ) Tally

    Where dateadd(dd,N-1,@start_dte) <= @end_dte

    )

    select count(*) AS NoOfBussinessDays

    from cCalender c

    Where Not Exists ( Select 1

    From @WeekDayTable w

    Where c.WeekDayName = w.ColWeekDayName

    And ColIsWeekEnd = 0

    )

    And Not Exists ( select 1

    from @HolidayTable h

    where h.HolidayDt = c.CalenderDate

    )

    Hope it helps

  • Thanks for the replies.

    I have a calendar table.

    When I calculate 20 days from the start date to get my end date.

    Then add my hols and weekends to get the new end date it just feels like this might be an endless cycle to get the eventual end date.

    Hope I'm making sense.

    Thanks in advance

  • Then add my hols and weekends to get the new end date it just feels like this might be an endless cycle to get the eventual end date.

    What exactly you have implemented can you share us the code?

Viewing 5 posts - 1 through 4 (of 4 total)

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