Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 20 day count Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, April 10, 2014 5:01 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, January 5, 2015 2:16 AM Points: 6, Visits: 21
 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
Post #1560356
 Posted Thursday, April 10, 2014 5:33 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, August 1, 2016 11:47 PM Points: 201, Visits: 201
 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 = 0where 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 startdateset @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 Repeatselect SUM(WorkingDayind) from #tempselect * from #tempdrop 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
Post #1560370
 Posted Thursday, April 10, 2014 6:41 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 5:38 AM Points: 1,341, Visits: 2,545
 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 @WeekDayTableSelect 1,'Sunday' , 0 union allSelect 2,'Monday' , 1 union allSelect 3,'Tuesday' , 1 union allSelect 4,'Wednesday', 1 union allSelect 5,'Thursday' , 1 union allSelect 6,'Friday' , 1 union allSelect 7,'Saturday' , 0;insert into @HolidayTableSelect '02-Apr-2014';;With cCalenderAS( 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 NoOfBussinessDaysfrom cCalender cWhere 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
Post #1560396
 Posted Thursday, April 10, 2014 7:16 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, January 5, 2015 2:16 AM Points: 6, Visits: 21
 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
Post #1560415
 Posted Friday, April 11, 2014 12:14 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 5:38 AM Points: 1,341, Visits: 2,545
 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?
Post #1560713

 Permissions