add working days to a date

I want to be able to pass a date to a function and add 4 days to it. The
quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
so
I have a table of Holidays with 2 records in 2/1/2006 (2nd Jan) and 3/1/2005
(3rd Jan)
The 31/12/05 and 1/1/06 are a Sat and Sun
I have a date 30/12/2005 to which I want to add 4 working days (this to be
variable) therefore the date returned would be 9/1/06 (9th Jan)
In vb I made use of the Wday function and DLookup
How would I do the same thing in SQL
Thanks
CREATE TABLE [dbo].[tblHols] (
[Holiday] [datetime] NOT NULL
) ON [PRIMARY]
GO>I want to be able to pass a date to a function and add 4 days to it. The
>quirk is that it needs to be 4 working days ie. Mon - Fri excl holidays.
Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
for a thorough treatment (I think one of the examples even shows you how to
estimate delivery date, which sounds pretty much like what you're asking
for).|||I have created the calendar table as suggested but the select query takes 43
secs to run. How could I speed this up? A similar function in VB takes far
less time.
Here are the scripts
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
isWday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20060101'
WHILE @.dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
UPDATE dbo.Calendar SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 1
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 2
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 3
UPDATE Calendar
SET
isHoliday = 1,
WHERE M = 1
AND D = 4
Declare @.dte datetime
SET @.dte = '20060101'
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nltssPAGHA.2984@.TK2MSFTNGP09.phx.gbl...
> Your best bet is a calendar table. Please see http://www.aspfaq.com/2519
> for a thorough treatment (I think one of the examples even shows you how
> to estimate delivery date, which sounds pretty much like what you're
> asking for).
>|||On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:

>I have created the calendar table as suggested but the select query takes 4
3
>secs to run. How could I speed this up? A similar function in VB takes fa
r
>less time.
Hi Newbie,
Just to clarify: you ARE aware that the Calendar table should be a
permanent one, aren't you? Create and populate it once, then just use
it. Don't drop after use and re-create before the next use.

>Declare @.dte datetime
>SET @.dte = '20060101'
>SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
To speed this up, try this modification:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
For a real speed gain, find a reasonable ratio for non-business days vs
total days and round up to be on the safe side. To be on the safe side,
I'll use a ratio of 1 to 2:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 9 * 2, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Having just posted a different project regarding dates, I thought I would gi
ve this a shoot. It turned into more work then expected, but should be nice
for a toolbox. This solution appears to run in the millisecond range and I
could probably get it faster. Currently, for holidays, I am calculating th
em each call. I could cache using a hashtable or something, but the speed s
till seems to be ok in any event. Attached is a class that contains many bus
iness date functions such as:
public static DateTime AddQuarters(DateTime date, int quarters);
public static DateTime AddWorkDays(DateTime startDate, int workDays, Holiday
[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, DateTim
e[] holidays);
public static DateTime AddWorkDays(DateTime startDate, int workDays, bool ho
lidaysAreWorkDays);
public static string DaysHoursMinutesSecondsMilliseconds(Date
Time start, Dat
eTime end);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW[] days
OfTheW);
public static DateTime[] GetDayOfWInMonth(DateTime date, DayOfW dayOfW
);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW dayOfW);
public static DateTime[] GetDayOfWInRange(DateTime startDate, DateTime en
dDate, DayOfW[] daysOfW);
public static DateTime GetEndOf(PeriodType periodType, DateTime date);
public static DateTime GetEndOfDay(DateTime date);
public static DateTime GetEndOfMinute(DateTime date);
public static DateTime GetEndOfMonth(DateTime date);
public static DateTime GetEndOfQuarter(DateTime date);
public static DateTime GetEndOfQuarter(int year, int quarter);
public static DateTime GetEndOfW(DateTime date);
public static DateTime GetEndOfYear(DateTime date);
public static DateTime GetEndOfYear(int Year);
public static Holiday[] GetHolidays(int year);
public static Holiday[] GetHolidays(DateTime startDate, DateTime endDate);
private static DateTime GetNextQuarter(DateTime date);
private static DateTime GetPriorQuarter(DateTime date);
public static int GetQuarter(DateTime date);
public static DateTime GetStartOf(PeriodType periodType, DateTime date);
public static DateTime GetStartOfDay(DateTime date);
public static DateTime GetStartOfMinute(DateTime date);
public static DateTime GetStartOfMonth(DateTime date);
public static DateTime GetStartOfQuarter(DateTime date);
public static DateTime GetStartOfQuarter(int year, int quarter);
public static DateTime GetStartOfW(DateTime date);
public static DateTime GetStartOfYear(DateTime date);
public static DateTime GetStartOfYear(int Year);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, Holiday[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, DateTime[] holidays);
public static DateTime[] GetWorkDaysInRange(DateTime startDate, DateTime end
Date, bool holidaysAreWorkDays);
public static string HoursAndMinutes(DateTime start, DateTime end);
public static bool IsBetweenDay(DateTime date, DateTime day);
public static bool IsBetweenDays(DateTime date, DateTime startDate, DateTime
endDate);
public static bool IsBetweenMonth(DateTime date, DateTime month);
public static bool IsBetweenQuarter(DateTime date, int quarter);
public static bool IsBetweenQuarter(DateTime date, int year, int quarter);
public static bool IsHoliday(DateTime date);
public static bool IsHoliday(DateTime date, Holiday[] holidays);
public static bool IsHoliday(DateTime date, DateTime[] holidays);
public static bool IsWorkDay(DateTime date);
// The following is a CLR UDF that calls this class for AddWorkDays. This e
xample udf calls DateFunctions.AddWorkDays() and skips any of the 10 Federal
holidays. You could also add your own holidays (i.e. non-work days) add ca
ll a different overload. Other UDFs that call the different static methods
on the DateFunctions class could be written as well.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlUtils;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static DateTime AddWorkDays(DateTime date, int workDays)
{
DateTime start = DateTime.Now;
DateTime day = DateFunctions.AddWorkDays(date, workDays, false);
DateTime end = DateTime.Now;
TimeSpan ts = end - start;
Console.WriteLine(ts.ToString());
return day;
}
};
Please let me know if you have any questions. I still need more testing on
everything, but the AddWorkDays seems to be working pretty well. Let me kno
w if you questions. Cheers!
--
William Stacey [MVP]|||Brilliant! It now takes less than 1 second when using the second mod
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:o0v3q1t8ckqoo7em98ull7k3s0vnrglksg@.
4ax.com...
> On Thu, 15 Dec 2005 09:52:05 -0000, Newbie wrote:
>
> Hi Newbie,
> Just to clarify: you ARE aware that the Calendar table should be a
> permanent one, aren't you? Create and populate it once, then just use
> it. Don't drop after use and re-create before the next use.
>
> To speed this up, try this modification:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> For a real speed gain, find a reasonable ratio for non-business days vs
> total days and round up to be on the safe side. To be on the safe side,
> I'll use a ratio of 1 to 2:
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 9 * 2, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is another fun little thing you can do with the lib. Print a calendar o
f any month(s). This basically works by enumerating the Calendar ws for a
ny month using GetMonthCalendarWs as shown in code below:
Output
---
January 2006
SU MO TU WE TH FR SA
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
February 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28
March 2006
SU MO TU WE TH FR SA
01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
...
// Put in windows or console app. Reference the sqlutils.dll and run.
private void button2_Click(object sender, EventArgs e)
{
Console.WriteLine();
for (int i = 1; i <= 12; i++)
{
PrintMonthCalendar(new DateTime(2006, i, 1));
Console.WriteLine();
}
}
private void PrintMonthCalendar(DateTime date)
{
Console.WriteLine(date.ToString("MMMM yyyy"));
Console.WriteLine("SU MO TU WE TH FR SA");
DateRange[] cal = DateRange.GetMonthCalendarWs(date, false);
for (int i = 0; i < cal.Length; i++)
{
DateRange day = cal[i];
if (day == null)
Console.Write(" " + " ");
else
Console.Write(day.StartDate.Day.ToString().PadLeft(2, '0') + " ");
if ((i != 0) && ((i + 1) % 7 == 0))
Console.WriteLine();
}
}
--
William Stacey [MVP]