Working Days

7 Sep 2006 6:53 AM

Phil

Hi,

I wonder if someone can help me with a date Query, I have read a few threads
how to find out the number of working days between 2 dates but what I want to
do is add 15 working days to a specific date, can anyone help I am using SQL
Server 2000.

Thanks PD

7 Sep 2006 6:58 AM

Arnie Rowland

You may benefit from the use of a Calendar Table.

See:

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

Datetime -How to count the number of business days
http://www.aspfaq.com/show.asp?id=2453

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Phil" <P***@discussions.microsoft.com> wrote in message
news:E6227D5D-E071-4A18-8440-9C7F29FDED15@microsoft.com...
> Hi,
>
> I wonder if someone can help me with a date Query, I have read a few
> threads
> how to find out the number of working days between 2 dates but what I want
> to
> do is add 15 working days to a specific date, can anyone help I am using
> SQL
> Server 2000.
>
> Thanks PD

7 Sep 2006 1:35 PM

Alexander Kuznetsov

Arnie Rowland wrote:

> You may benefit from the use of a Calendar Table.
>
> See:
>
> Datetime -Calendar Table
> http://www.aspfaq.com/show.asp?id=2519
>
> Datetime -How to count the number of business days
> http://www.aspfaq.com/show.asp?id=2453
>

Note that if you are dealing wil large date ranges, such as 20 days or
more,
you can speed up your queries if you add to your Calendar table another
column NumBusinessDay.

SELECT DateFrom, IsBusinessDay D, NumBusinessDay FROM Dates WHERE
[DateFrom] BETWEEN '20061220' AND '20061230'

DateFrom                                               D
NumBusinessDay
------------------------------------------------------ ----
--------------
2006-12-20 00:00:00.000                                Y    86
2006-12-21 00:00:00.000                                Y    87
2006-12-22 00:00:00.000                                Y    88
2006-12-23 00:00:00.000                                N    88
2006-12-24 00:00:00.000                                N    88
2006-12-25 00:00:00.000                                N    88
2006-12-26 00:00:00.000                                Y    89
2006-12-27 00:00:00.000                                Y    90
2006-12-28 00:00:00.000                                Y    91
2006-12-29 00:00:00.000                                Y    92
2006-12-30 00:00:00.000                                N    92

(11 row(s) affected)

-- 5 business days from 20061220
SELECT DateFrom FROM Dates WHERE NumBusinessDay =
(SELECT NumBusinessDay FROM Dates WHERE [DateFrom] = '20061220') + 5
AND IsBusinessDay = 'Y'

DateFrom
------------------------------------------------------
2006-12-28 00:00:00.000

(1 row(s) affected)

-- number of business days between '20061220' and '20061230'

SELECT (SELECT NumBusinessDay FROM Dates WHERE [DateFrom] = '20061230')
-
(SELECT NumBusinessDay FROM Dates WHERE [DateFrom] = '20061220')
BusinessDaysBetween

BusinessDaysBetween
-------------------
6

(1 row(s) affected)