SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


20 day count


20 day count

Author
Message
duncan.turner.2dg
duncan.turner.2dg
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
BI_Baracus
BI_Baracus
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 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 = 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
twin.devil
twin.devil
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 2675
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' , 1 union all
Select 3,'Tuesday' , 1 union all
Select 4,'Wednesday', 1 union all
Select 5,'Thursday' , 1 union all
Select 6,'Friday' , 1 union 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
duncan.turner.2dg
duncan.turner.2dg
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
twin.devil
twin.devil
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 2675

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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search