'NETWORKDAYS(start_date,end_date,holidays)' in SQL ?

[Ryan]

Is there an equivalent function in SQL to the
'NETWORKDAYS(st art_date,end_da te,holidays)' function in Excel ?

NetworkDays in Excel returns the number of whole working days between
start_date and end_date. Working days exclude weekends and any dates
identified in holidays. This part isn't vital but would be nice to
have. The weekend stuff is more important.

I realise this will most likely need to be some method I write myself
(possibly based on the name of the day - Mon-Fri), but any pointers
would be appreciated if anyone has done this before.

Thanks

Ryan

[David Portas]

Re: 'NETWORKDAYS(st art_date,end_da te,holidays)' in SQL ?

It helps to have a Calendar table in your database. It can be used to
support many queries such as this:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT
'Y')

Populate it with as many years as you'll ever need:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'2100 1231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATED IFF(D,'19991231 ',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Set the non-working days:

UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,cal date) IN ('Saturday','Su nday')

Record any public holidays in the same way.

Now you can easily compute the number of working days between two
dates:

SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @first_date AND @second_date
AND workingday = 'Y' ;

--
David Portas
SQL Server MVP
--

[Ryan]

Re: 'NETWORKDAYS(st art_date,end_da te,holidays)' in SQL ?

Perfect. Thanks !

R

David Portas wrote:[color=blue]
> It helps to have a Calendar table in your database. It can be used to
> support many queries such as this:
>
> CREATE TABLE Calendar
> (caldate DATETIME NOT NULL PRIMARY KEY,
> workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N'))[/color]
DEFAULT[color=blue]
> 'Y')
>
> Populate it with as many years as you'll ever need:
>
> INSERT INTO Calendar (caldate) VALUES ('20000101')
>
> WHILE (SELECT MAX(caldate) FROM Calendar)<'2100 1231'
> INSERT INTO Calendar (caldate)
> SELECT DATEADD(D,DATED IFF(D,'19991231 ',caldate),
> (SELECT MAX(caldate) FROM Calendar))
> FROM Calendar
>
> Set the non-working days:
>
> UPDATE Calendar SET workingday = 'N'
> WHERE DATENAME(DW,cal date) IN ('Saturday','Su nday')
>
> Record any public holidays in the same way.
>
> Now you can easily compute the number of working days between two
> dates:
>
> SELECT COUNT(*)
> FROM Calendar
> WHERE caldate BETWEEN @first_date AND @second_date
> AND workingday = 'Y' ;
>
> --
> David Portas
> SQL Server MVP
> --[/color]