NETWORKDAYS_q

Updated 2023-11-09 21:03:19.750000

Syntax

SELECT [westclintech].[wct].[NETWORKDAYS_q] (
  <@StartDate, datetime,>
 ,<@EndDate, datetime,>
 ,<@HolidayDates_RangeQuery, nvarchar(max),>)

Description

Use the scalar function NETWORKDAYS_q to calculate the number of whole working days between a start date and an end date. Working days exclude weekends (Saturday and Sunday) and dates identified as holidays.

Arguments

@EndDate

he ending date for the calculation. Must be of data types that are implicitly convertible to datetime.

@HolidayDates_RangeQuery

the select statement, as text, which will return a resultant table containing the holiday dates. The resultant table can only contain values of a datetime date type or that will implicitly convert to datetime.

@StartDate

The starting date for the calculation. Must be of data types that are implicitly convertible to datetime.

Return Type

float

Remarks

For simpler queries, use NETWORKDAYS.

If @StartDate > @Enddate, the result will be negative.

The calendar may be omitted by entering '' in the @HolidayDates_RangeQuery.

Saturday and Sunday as assumed to be non-working days.

Examples

SELECT wct.NETWORKDAYS_q(
                            '12/31/2009',
                            '02/16/2010',
                            'SELECT ''1/1/2010'' UNION ALL
      SELECT ''1/18/2010'' UNION ALL
      SELECT ''2/15/2010'' UNION ALL
      SELECT ''4/2/2010'' UNION ALL
      SELECT ''5/31/2010'' UNION ALL
      SELECT ''7/5/2010'' UNION ALL
      SELECT ''9/6/2010'' UNION ALL
      SELECT ''11/25/2010'' UNION ALL
      SELECT ''12/24/2010'''
                        );

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"31"}]}

Or, if the holidays were stored in a table:

CREATE TABLE #t
(
    Holiday datetime
);
INSERT INTO #t
SELECT '1/1/2010'
UNION ALL
SELECT '1/18/2010'
UNION ALL
SELECT '2/15/2010'
UNION ALL
SELECT '4/2/2010'
UNION ALL
SELECT '5/31/2010'
UNION ALL
SELECT '7/5/2010'
UNION ALL
SELECT '9/6/2010'
UNION ALL
SELECT '11/25/2010'
UNION ALL
SELECT '12/24/2010';
SELECT wct.NETWORKDAYS_q('12/31/2009', '02/16/2010', 'SELECT * FROM #t');

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"31"}]}

If you did not wish to specify a holiday calendar, it can be omitted from the calculation by entering blank ('') in @HolidayDates_RangeQuery .

SELECT wct.NETWORKDAYS_q('12/31/2009', '02/16/2010', '');

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"34"}]}

If the start date is less than the end date, the function returns a negative number.

SELECT wct.NETWORKDAYS_q(
                            '02/16/2010',
                            '12/31/2009',
                            REPLACE(
                                       'SELECT ''1/1/2010''
      ''1/18/2010''
      ''2/15/2010''
      ''4/2/2010''
      ''5/31/2010''
      ''7/5/2010''
      ''9/6/2010''
      ''11/25/2010''
''12/24/2010''',
                                       CHAR(13) + CHAR(10),
                                       ' UNION ALL' + CHAR(10) + 'SELECT '
                                   )
                        );

This produces the following result.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"-31"}]}