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"}]}