Hello Ilias,
A short while ago I needed to calculate the number of working days
between (and not including) two specified dates.
To meet my needs I...
A) found a SQL function (GetWorkingDays) on the Internet that
returns the number of working days,
B) wrote a second function (GetNonWorkingDays) to count the number
of non-working days for an empty reference project
(ZZZ-BPSU-Reference.Published), and
C) wrote a third function (GetProjectWorkDays) that calls the first two
functions ad return the difference.
In my application I issue the SQL command
Select GetProjectWorkDays("07-01-2005" , "12-31-2005")
which returns the number of working days between
(and not including) these dates using the project calendar for my
reference project.
I hope this helps or gives you an idea of where to start... The SQL
functions are attached below my signature block.
Bob Segrest, PMP
Microsoft Project Blackbelt
----------------------------------
CREATE FUNCTION GetWorkingDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
BEGIN
RETURN ( 0 )
END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 5
WHEN 'Monday' THEN 4
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 2
WHEN 'Thursday' THEN 1
WHEN 'Friday' THEN 0
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
RETURN ( @WorkDays )
END
CREATE FUNCTION GetNonWorkingDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
declare @Days int
BEGIN
select
@Days = count(*)
from
msp_projects p,
msp_calendar_data c
where
proj_name like 'ZZZ-BPSU-Reference.Published'
and c.proj_id = p.proj_id
and cal_uid = 1
and cd_working = 0
and cd_from_date > @startDate
and cd_to_date < @endDate
END
return @Days
END
CREATE FUNCTION GetProjectWorkDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
declare @projectWorkDays int
select @projectWorkDays = (SELECT GetWorkingDays (@startDate, @endDate))
- (SELECT GetNonWorkingDays (@startDate, @endDate))
return @projectWorkDays
end
----------------------------------
