BUSDAYSWE
Updated 2023-10-06 23:26:55.847000
Syntax
SELECT [westclintech].[wct].[BUSDAYSWE](
<@StartDate, datetime,>
,<@EndDate, datetime,>
,<@Holidays, nvarchar(max),>
,<@WE1, int,>
,<@WE2, int,>)
Description
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.
Arguments
@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.
@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.
@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.
@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.
@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.
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.
{"columns":[{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"Month"},{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Month":"January","Business Days":"25"},{"Month":"February","Business Days":"23"},{"Month":"March","Business Days":"26"},{"Month":"April","Business Days":"26"},{"Month":"May","Business Days":"26"},{"Month":"June","Business Days":"25"},{"Month":"July","Business Days":"26"},{"Month":"August","Business Days":"26"},{"Month":"September","Business Days":"25"},{"Month":"October","Business Days":"26"},{"Month":"November","Business Days":"23"},{"Month":"December","Business Days":"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.
{"columns":[{"field":"Month"},{"field":"Business Days","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Month":"January","Business Days":"22"},{"Month":"February","Business Days":"20"},{"Month":"March","Business Days":"22"},{"Month":"April","Business Days":"21"},{"Month":"May","Business Days":"23"},{"Month":"June","Business Days":"21"},{"Month":"July","Business Days":"21"},{"Month":"August","Business Days":"23"},{"Month":"September","Business Days":"20"},{"Month":"October","Business Days":"23"},{"Month":"November","Business Days":"21"},{"Month":"December","Business Days":"20"}]}