BUSDAYSWE
Updated: 05 May 2014
Use the scalar function BUSDAYSWE to calculate the number of business days from a start date (inclusive) to an end date (exclusive), where the weekend days are not Saturday and Sunday. BUSDAYSWE lets you specify one or two consecutive days of the week as weekend days.
Syntax
SELECT [wctFinancial].[wct].[BUSDAYSWE](
<@StartDate, datetime,>
,<@EndDate, datetime,>
,<@Holidays, nvarchar(max),>
,<@WE1, int,>
,<@WE2, int,>)
Arguments
@StartDate
the start date for the calculation. @StartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@EndDate
the end date for the calculation. @EndDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Holidays
a comma separated string containing the holiday (non-business) dates to be used in the calculation of the number of business days. You can use the aggregate function NBD to create an appropriately formatted string.
@WE1
the first weekend day. Sunday is day 0; Saturday is day 6. @WE1 is an expression of type int or of a type that can be implicitly converted to int.
@WE2
the second weekend day. Sunday is day 0; Saturday is day 6. @WE2 is an expression of type int or of a type that can be implicitly converted to int.
Return Type
int
Remarks
· @StartDate cannot be NULL
· @EndDate cannot be NULL
· If @WE1 IS NULL and @WE2 IS NULL then @WE1 = 6 and @WE2 = 0
· If @WE2 is NULL and @WE1 is NOT NULL then @WE2 = @WE1
· If @WE1 is NULL and @WE2 is NOT NULL then @WE1 = @WE2
· @WE1 must be between 0 and 6
· @WE2 must be between 0 and 6
Examples
In this example we calculate the number of business days from Wednesday to Wednesday assuming that the only weekend day is Sunday.
SELECT
wct.BUSDAYSWE(
'2014-05-01', --@Startdate
'2014-05-08', --@Enddate
NULL, --@Holidays
0, --@WE1
NULL --@WE2
) as [Business Days]
This produces the following result.
Business Days
-------------
6
In this example, we add some holidays, and we calculate the number number of business days in the month of May, assuming that the only weekend day is Sunday.
DECLARE @h as varchar(max)
SET @h =(
SELECT
wct.NBD(d)
FROM (
SELECT '2014-01-01' UNION ALL
SELECT '2014-01-20' UNION ALL
SELECT '2014-02-17' UNION ALL
SELECT '2014-05-26' UNION ALL
SELECT '2014-07-04' UNION ALL
SELECT '2014-09-01' UNION ALL
SELECT '2014-10-13' UNION ALL
SELECT '2014-11-11' UNION ALL
SELECT '2014-11-27' UNION ALL
SELECT '2014-12-25'
) n(d)
)
SELECT
wct.BUSDAYSWE(
'2014-05-01', --@Startdate
'2014-06-01', --@Enddate
@h, --@Holidays
0, --@WE1
NULL --@WE2
) as [Business Days]
This produces the following result.
Business Days
-------------
26
In this example, we calculate the number of business days for each month in 2014 assuming that the only weekend day is Sunday.
DECLARE @h as varchar(max)
SET @h =(
SELECT
wct.NBD(d)
FROM (
SELECT '2014-01-01' UNION ALL
SELECT '2014-01-20' UNION ALL
SELECT '2014-02-17' UNION ALL
SELECT '2014-05-26' UNION ALL
SELECT '2014-07-04' UNION ALL
SELECT '2014-09-01' UNION ALL
SELECT '2014-10-13' UNION ALL
SELECT '2014-11-11' UNION ALL
SELECT '2014-11-27' UNION ALL
SELECT '2014-12-25'
) n(d)
)
SELECT
DATENAME(m,StartDate) as [Month],
wct.BUSDAYSWE(StartDate,enddate,@h,0,NULL) as [Business Days]
FROM (
SELECT
wct.CALCDATE(2014,m,1) as StartDate,
wct.EDATE(wct.CALCDATE(2014,m,1),1) as EndDate
FROM (
VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
)n(m)
)p
This produces the following result.
Month Business Days
------------------------------ -------------
January 25
February 23
March 26
April 26
May 26
June 25
July 26
August 26
September 25
October 26
November 23
December 26
In this example, the weekend days are Monday and Tuesday.
DECLARE @h as varchar(max)
SET @h =(
SELECT
wct.NBD(d)
FROM (
SELECT '2014-01-01' UNION ALL
SELECT '2014-01-20' UNION ALL
SELECT '2014-02-17' UNION ALL
SELECT '2014-05-26' UNION ALL
SELECT '2014-07-04' UNION ALL
SELECT '2014-09-01' UNION ALL
SELECT '2014-10-13' UNION ALL
SELECT '2014-11-11' UNION ALL
SELECT '2014-11-27' UNION ALL
SELECT '2014-12-25'
) n(d)
)
SELECT
DATENAME(m,StartDate) as [Month],
wct.BUSDAYSWE(StartDate,enddate,@h,1,2) as [Business Days]
FROM (
SELECT
wct.CALCDATE(2014,m,1) as StartDate,
wct.EDATE(wct.CALCDATE(2014,m,1),1) as EndDate
FROM (
VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
)n(m)
)p
This produces the following result.
Month Business Days
------------------------------ -------------
January 22
February 20
March 22
April 21
May 23
June 21
July 21
August 23
September 20
October 23
November 21
December 20