Wiki

None

None Defined

Calc Ending Business Days

Need proc to figure out current date + business days ending on business day

create procedure TWBS_AddBusinessDays (@StartDate DateTime ,@Days int) as

    declare @weeks int
  select @Weeks = 
          case when  cast(@Days/ 5 as int) <> @days/5 
          then @Days / 5 
          else @Days / 5-1 end

    declare @EndDate DateTime
          

select @EndDate = case datepart(weekday, dateadd(dd, @Days, @StartDate ) + @Weeks *2 +2)

                when 1 then dateadd(dd, @Days, @StartDate ) + @Weeks *2   +2 +1
                when 7 then dateadd(dd, @Days, @StartDate ) + @Weeks *2   +2+2
                else dateadd(dd, @Days, @StartDate ) + @Weeks *2   +2
                end

declare @EndDate1 datetime select @EndDate1 = @EndDate + count(*) from hld1 where strdate between @StartDate and @EndDate select case datepart(weekday, @EndDate1)

          when 1 then @EndDate1 + 1
          when 7 then @EndDate1 + 2
          else @EndDate1 end

Solution

17 Mar 2009