Calculate Days for Date range without Holi/Weekend

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate Days for Date range without Holi/Weekend

Author

Topic

ubravo
Starting Member

6 Posts

Posted - 2006-06-28 : 14:05:13

I'm trying to calculate the difference between two date fields, but exclude weekend days and holidays from the calculation.

I have a Holiday Table named "CalendarDetail" which have the next fields
ID CalendarID Date Description Icon
259 9 2006-01-01 00:00:00.000 New Years NewYear.gif
260 9 2006-02-05 00:00:00.000 Feb 5 holdiay Generic.gif
261 9 2006-03-21 00:00:00.000 March 21 HolidayGeneric.gif
262 9 2006-05-01 00:00:00.000 May 1 Holiday Generic.gif
263 9 2006-09-16 00:00:00.000 Sept 16 Holiday Generic.gif
264 9 2006-11-20 00:00:00.000 Nov 20 Holiday Generic.gif
265 9 2006-12-25 00:00:00.000 Dec 25 Holiday Generic.gif

Should I make use of Stored Procedures or UDF ? Any Idea how to accomplish this?

Thanks!

ubravo
Starting Member

6 Posts

Posted - 2006-06-28 : 14:15:40

Hi All! I figure it out... I post this because maybe could help someone, regards.

CREATE function GetWorkingDays_NoWeekend_NoHoliday(
@startDate datetime,
@endDate datetime
)
RETURNS INT
AS
/*
Purpose:
Returns the count of working days or weekdays for the given date range, taking care of the holidays calendar and weekends History:
27.06.06 URIEL BRAVO, created the stored procedure
*/
BEGIN

DECLARE @iCountDays int

DECLARE @holidaycount INT;

SET @holidaycount = (SELECT Count(*) AS NumberOfHolidayDays FROM CalendarDetail
WHERE DateInfo between @startDate and @endDate )

DECLARE @range INT;

SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;

DECLARE @weekdayscount INT;
SET @weekdayscount =
(
SELECT
@range / 7 * 5 + @range % 7 -
(
SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
WHERE d <= @range % 7
AND DATENAME(WEEKDAY, @endDate - d + 1)
IN
(
'Saturday',
'Sunday'
)
)
);

SET @iCountDays = (@weekdayscount-@holidaycount)

RETURN @iCountDays
END

--TO TEST IT
PRINT dbo.GetWorkingDays_NoWeekend_NoHoliday ('06/01/2006', '06/15/2006')

Please try to post another ideas about this issue it's just the beginning, but I note that there is a lot of pleople trying to found some solutions like this in the web maybe using an SP, Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 14:20:12

quote:
Originally posted by ubravo

Hi All! I figure it out... I post this because maybe could help someone, regards.

I think you have to do some more work. What if holiday is ON a weekend?

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-28 : 14:31:47

Look here

http://weblogs.sqlteam.com/brettk/archive/2004/12/20/3751.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-28 : 15:39:09

It is fairly easy with a date table and a holiday table.

The Date Table Function F_TABLE_DATE is available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


declare @start_date	datetime
declare @end_date	datetime
select @start_date	= '20060601'
select @end_date	= '20060801'

declare @holiday table	
([HOLIDAY]  datetime not null primary key clustered )

insert into @holiday
select '20060704' -- July 4th Holiday

select
	[Days Between] = count(*)-1
from
	-- Script available from Script Library Forum
	dbo.F_TABLE_DATE ( @start_date,@end_date) a
	left join
	@holiday b
	on a.[DATE] = b.[HOLIDAY]
where
	b.[HOLIDAY] is null and
	[DAY_OF_WEEK]	 between 2 and 6

Results:



Days Between 
------------ 
42

(1 row(s) affected)

CODO ERGO SUM

yvesj
Starting Member

1 Post

Posted - 2006-07-12 : 01:03:36

To calculate the number of workdays between 2 dates:

select datediff(dd,@startdate,@enddate)-2*datediff(ww,@startdate,@enddate)
+ case when datepart(dw,@startdate)=1 or datepart(dw,@enddate)=7 then -1
when datepart(dw,@startdate)=7 or datepart(dw,@enddate)=1 then +1
else 0 end

The statement above and the functions below assume that SET DATEFIRST is left at the US default value of 7 (first day of the week is Sunday)

Below are functions that return the number of workdays (exclude Saturdays and Sundays) or the number of business days (exclude Saturdays, Sundays and holidays) between 2 dates.

create function dbo.weekdays (@date_1 smalldatetime, @date_2 smalldatetime)
returns int as
begin
declare @st smalldatetime, @nd smalldatetime
set @st = @date_1 set @nd = @date_2 if @st > @nd begin set @nd = @date_1 set @st = @date_2 end

return datediff(dd,@st,@nd)-2*datediff(ww,@st,@nd)
+ case when datepart(dw,@st)=1 or datepart(dw,@nd)=7 then -1
when datepart(dw,@st)=7 or datepart(dw,@nd)=1 then +1
else 0 end
end

create function dbo.bizdays (@date_1 smalldatetime, @date_2 smalldatetime)
returns int as
begin
declare @st smalldatetime, @nd smalldatetime
set @st = @date_1 set @nd = @date_2 if @st > @nd begin set @nd = @date_1 set @st = @date_2 end

return datediff(dd,@st,@nd)-2*datediff(ww,@st,@nd)
+ case when datepart(dw,@st)=1 or datepart(dw,@nd)=7 then -1
when datepart(dw,@st)=7 or datepart(dw,@nd)=1 then +1
else 0 end
- (select count(*) from holidays where datepart(dw,holiday_date) not in (1,7)
and holiday_date between @st and @nd)
end

Test of weekday function:

select dbo.weekdays('2006-07-07','2006-07-08') fri_sat,
dbo.weekdays('2006-07-07','2006-07-09') fri_sun,
dbo.weekdays('2006-07-07','2006-07-10') fri_mon,
dbo.weekdays('2006-07-08','2006-07-10') sat_mon,
dbo.weekdays('2006-07-09','2006-07-10') sun_mon,
dbo.weekdays('2006-07-02','2006-07-08') sun_sat,
dbo.weekdays('2006-07-08','2006-07-09') sat_sun_consecutive,
dbo.weekdays('2006-07-01','2006-07-09') sat_sun_non_consecutive

fri_sat fri_sun fri_mon sat_mon sun_mon sun_sat sat_sun_consecutive sat_sun_non_consecutive
======= ======= ======= ======= ======= ======= =================== =======================
0 1 1 1 0 5 0 5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources