BUSDAYS
Updated: 11 May 2012
Use the scalar function BUSDAYS to calculate the number of business days from a start date (inclusive) to an end date (exclusive).
Syntax
SELECT [wctFinancial].[wct].[BUSDAYS] (
<@StartDate, datetime,>
,<@EndDate, datetime,>
,<@Holidays, nvarchar(max),>)
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 NDB to create an appropriately formatted string.
Return Type
int
Remarks
· @StartDate cannot be NULL
· @EndDate cannot be NULL
· Saturday and Sunday are always treated as non-business days.
Examples
In this example we will put the holiday string into a variable, using the NBD function and calculate the number of business days and the actual number of days between different date combinations.
/*Populate the variable*/
DECLARE @h as varchar(max)
SET @h = (SELECT wct.NBD(d)
FROM (
SELECT '2010-01-01' UNION ALL
SELECT '2010-01-18' UNION ALL
SELECT '2010-02-15' UNION ALL
SELECT '2010-05-31' UNION ALL
SELECT '2010-07-05' UNION ALL
SELECT '2010-09-06' UNION ALL
SELECT '2010-10-11' UNION ALL
SELECT '2010-11-25' UNION ALL
SELECT '2010-12-24' UNION ALL
SELECT '2010-12-31' UNION ALL
SELECT '2011-01-17' UNION ALL
SELECT '2011-02-21' UNION ALL
SELECT '2011-05-30' UNION ALL
SELECT '2011-07-04' UNION ALL
SELECT '2011-09-05' UNION ALL
SELECT '2011-10-10' UNION ALL
SELECT '2011-10-11' UNION ALL
SELECT '2011-11-24' UNION ALL
SELECT '2011-12-26' UNION ALL
SELECT '2012-01-02' UNION ALL
SELECT '2012-01-16' UNION ALL
SELECT '2012-02-20' UNION ALL
SELECT '2012-05-28' UNION ALL
SELECT '2012-07-04' UNION ALL
SELECT '2012-09-03' UNION ALL
SELECT '2012-10-08' UNION ALL
SELECT '2012-11-12' UNION ALL
SELECT '2012-11-22' UNION ALL
SELECT '2012-12-25' UNION ALL
SELECT '2013-01-01' UNION ALL
SELECT '2013-01-21' UNION ALL
SELECT '2013-02-18' UNION ALL
SELECT '2013-05-27' UNION ALL
SELECT '2013-07-04' UNION ALL
SELECT '2013-09-02' UNION ALL
SELECT '2013-10-14' UNION ALL
SELECT '2013-11-11' UNION ALL
SELECT '2013-11-28' UNION ALL
SELECT '2013-12-25' UNION ALL
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' UNION ALL
SELECT '2015-01-01' UNION ALL
SELECT '2015-01-19' UNION ALL
SELECT '2015-02-16' UNION ALL
SELECT '2015-05-25' UNION ALL
SELECT '2015-07-03' UNION ALL
SELECT '2015-09-07' UNION ALL
SELECT '2015-10-12' UNION ALL
SELECT '2015-11-11' UNION ALL
SELECT '2015-11-26' UNION ALL
SELECT '2015-12-25'
) n(d)
)
Calculate business days using the previously declared variable.
SELECT *
,wct.BUSDAYS([Start Date],[End Date],@h) as [Business Days]
,DATEDIFF(d,[Start Date],[End Date]) as Actual
FROM (
SELECT '2015-08-01','2014-11-19' UNION ALL
SELECT '2014-11-01','2015-04-12' UNION ALL
SELECT '2010-03-16','2015-12-12' UNION ALL
SELECT '2009-10-21','2011-11-24' UNION ALL
SELECT '2014-07-22','2015-10-13' UNION ALL
SELECT '2013-02-03','2012-02-27' UNION ALL
SELECT '2013-04-26','2015-01-23' UNION ALL
SELECT '2013-01-07','2011-06-07' UNION ALL
SELECT '2015-08-04','2010-09-23' UNION ALL
SELECT '2013-12-16','2015-08-25' UNION ALL
SELECT '2015-01-02','2013-08-07' UNION ALL
SELECT '2009-12-02','2011-10-07' UNION ALL
SELECT '2014-06-14','2015-10-23' UNION ALL
SELECT '2010-01-15','2015-03-13' UNION ALL
SELECT '2015-01-19','2010-06-07' UNION ALL
SELECT '2014-09-20','2012-12-31' UNION ALL
SELECT '2009-11-08','2014-09-01' UNION ALL
SELECT '2011-07-28','2015-09-24' UNION ALL
SELECT '2010-11-07','2011-06-08' UNION ALL
SELECT '2013-01-08','2014-11-26' UNION ALL
SELECT '2012-07-12','2015-02-04' UNION ALL
SELECT '2012-07-03','2012-08-30'
) m([Start Date],[End Date])
This produces the following result.
Start Date End Date Business Days Actual
---------- ---------- ------------- -----------
2015-08-01 2014-11-19 -176 -255
2014-11-01 2015-04-12 109 162
2010-03-16 2015-12-12 1444 2097
2009-10-21 2011-11-24 529 764
2014-07-22 2015-10-13 308 448
2013-02-03 2012-02-27 -236 -342
2013-04-26 2015-01-23 436 637
2013-01-07 2011-06-07 -397 -580
2015-08-04 2010-09-23 -1220 -1776
2013-12-16 2015-08-25 425 617
2015-01-02 2013-08-07 -351 -513
2009-12-02 2011-10-07 467 674
2014-06-14 2015-10-23 341 496
2010-01-15 2015-03-13 1294 1883
2015-01-19 2010-06-07 -1159 -1687
2014-09-20 2012-12-31 -434 -628
2009-11-08 2014-09-01 1211 1758
2011-07-28 2015-09-24 1044 1519
2010-11-07 2011-06-08 146 213
2013-01-08 2014-11-26 474 687
2012-07-12 2015-02-04 642 937
2012-07-03 2012-08-30 41 58
Let’s assume that we keep holidays on a table indexed by country code in our database. We could run the following SQL using CROSS APPLY to supply holiday information to the function.
SELECT [Start Date]
,[End Date]
,wct.BUSDAYS([Start Date],[End Date],h) as [Business Days]
,DATEDIFF(d,[Start Date],[End Date]) as Actual
FROM (
SELECT '2015-08-01','2014-11-19' UNION ALL
SELECT '2014-11-01','2015-04-12' UNION ALL
SELECT '2010-03-16','2015-12-12' UNION ALL
SELECT '2009-10-21','2011-11-24' UNION ALL
SELECT '2014-07-22','2015-10-13' UNION ALL
SELECT '2013-02-03','2012-02-27' UNION ALL
SELECT '2013-04-26','2015-01-23' UNION ALL
SELECT '2013-01-07','2011-06-07' UNION ALL
SELECT '2015-08-04','2010-09-23' UNION ALL
SELECT '2013-12-16','2015-08-25' UNION ALL
SELECT '2015-01-02','2013-08-07' UNION ALL
SELECT '2009-12-02','2011-10-07' UNION ALL
SELECT '2014-06-14','2015-10-23' UNION ALL
SELECT '2010-01-15','2015-03-13' UNION ALL
SELECT '2015-01-19','2010-06-07' UNION ALL
SELECT '2014-09-20','2012-12-31' UNION ALL
SELECT '2009-11-08','2014-09-01' UNION ALL
SELECT '2011-07-28','2015-09-24' UNION ALL
SELECT '2010-11-07','2011-06-08' UNION ALL
SELECT '2013-01-08','2014-11-26' UNION ALL
SELECT '2012-07-12','2015-02-04' UNION ALL
SELECT '2012-07-03','2012-08-30'
) m([Start Date],[End Date])
CROSS APPLY (
SELECT wct.NBD(Holiday) FROM HOLIDAYS
WHERE Country = 'US'
) k(h)
This produces the following result
Start Date End Date Business Days Actual
---------- ---------- ------------- -----------
2015-08-01 2014-11-19 -175 -255
2014-11-01 2015-04-12 108 162
2010-03-16 2015-12-12 1443 2097
2009-10-21 2011-11-24 528 764
2014-07-22 2015-10-13 308 448
2013-02-03 2012-02-27 -235 -342
2013-04-26 2015-01-23 436 637
2013-01-07 2011-06-07 -397 -580
2015-08-04 2010-09-23 -1220 -1776
2013-12-16 2015-08-25 425 617
2015-01-02 2013-08-07 -351 -513
2009-12-02 2011-10-07 467 674
2014-06-14 2015-10-23 341 496
2010-01-15 2015-03-13 1294 1883
2015-01-19 2010-06-07 -1158 -1687
2014-09-20 2012-12-31 -433 -628
2009-11-08 2014-09-01 1210 1758
2011-07-28 2015-09-24 1044 1519
2010-11-07 2011-06-08 146 213
2013-01-08 2014-11-26 474 687
2012-07-12 2015-02-04 642 937
2012-07-03 2012-08-30 41 58
Let’s look at what happens if the start date is a Saturday.
DECLARE @sd as datetime
DECLARE @h as varchar(max)
SET @sd = '2012-05-26'
SET @h = (SELECT wct.NBD(holiday) FROM HOLIDAYS WHERE COUNTRY = 'US')
SELECT RIGHT(Convert(varchar, @sd, 106), 11) as [Start Date]
,RIGHT(Convert(varchar, seriesvalue, 106), 11) as [End Date]
,wct.BUSDAYS(@sd, seriesvalue, @h) as [Business Days]
,DATEDIFF(d,@sd, seriesvalue) as [Actual Days]
FROM wctMath.wct.SeriesDate(@sd, '2012-06-06', NULL,NULL,NULL)
This produces the following result.
Start Date End Date Business Days Actual Days
----------- ----------- ------------- -----------
26 May 2012 26 May 2012 0 0
26 May 2012 27 May 2012 0 1
26 May 2012 28 May 2012 0 2
26 May 2012 29 May 2012 0 3
26 May 2012 30 May 2012 1 4
26 May 2012 31 May 2012 2 5
26 May 2012 01 Jun 2012 3 6
26 May 2012 02 Jun 2012 3 7
26 May 2012 03 Jun 2012 3 8
26 May 2012 04 Jun 2012 4 9
26 May 2012 05 Jun 2012 5 10
26 May 2012 06 Jun 2012 6 11
Notice that is not until 30-May-2012 that one business day has elapsed. There are 4 actual days from 26-May-2012 to 30-May-2012. 26-May-2012 is a Saturday, 27-May-2012 is a Sunday, and 28-May-2012 is holiday. Of the 4 days, 3 are non-business days, so the result is 1 business day.
Let’s look at what happens if the end date is a Sunday.
DECLARE @ed as datetime
DECLARE @h as varchar(max)
SET @ed = '2012-06-03'
SET @h = (SELECT wct.NBD(holiday) FROM HOLIDAYS WHERE COUNTRY = 'US')
SELECT RIGHT(Convert(varchar, seriesvalue, 106), 11) as [Start Date]
,RIGHT(Convert(varchar, @ed, 106), 11) as [End Date]
,wct.BUSDAYS(seriesvalue, @ed, @h) as [Business Days]
,DATEDIFF(d,seriesvalue, @ed) as [Actual Days]
FROM wctMath.wct.SeriesDate('2012-05-23', @ed, NULL,NULL,NULL)
This produces the following result.
Start Date End Date Business Days Actual Days
----------- ----------- ------------- -----------
23 May 2012 03 Jun 2012 6 11
24 May 2012 03 Jun 2012 5 10
25 May 2012 03 Jun 2012 4 9
26 May 2012 03 Jun 2012 3 8
27 May 2012 03 Jun 2012 3 7
28 May 2012 03 Jun 2012 3 6
29 May 2012 03 Jun 2012 3 5
30 May 2012 03 Jun 2012 2 4
31 May 2012 03 Jun 2012 1 3
01 Jun 2012 03 Jun 2012 0 2
02 Jun 2012 03 Jun 2012 0 1
03 Jun 2012 03 Jun 2012 0 0
Notice that there 11 actual days between 23-May-2012 and 03-Jun-2012, but there are only 6 business days. There are two Saturdays (26-May-2012 and 02-Jun-2012), two Sundays (27-May-2012 and 03-Jun-2012) and one holiday (28-May-2012).
See Also