|
|
add working days to a date |
|
Author |
Message |
wookie
|
Posted: Wed Dec 14 16:12:39 CST 2005 |
Top |
SQL Server Developer >> 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 Weekday function and DLookup
How would I do the same thing in SQL
Thanks
CREATE TABLE [dbo].[tblHols] (
[Holiday] [datetime] NOT NULL
) ON [PRIMARY]
GO
SQL Server152
|
|
|
|
|
Aaron
|
Posted: Wed Dec 14 16:12:39 CST 2005 |
Top |
SQL Server Developer >> 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.
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).
|
|
|
|
|
Newbie
|
Posted: Thu Dec 15 03:52:05 CST 2005 |
Top |
SQL Server Developer >> add working days to a date
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,
isWeekday 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
BEGIN
END
UPDATE dbo.Calendar SET
isWeekday = 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
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWeekday = 1
AND c.isHoliday =0
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
AND c2.dt <= c.dt
AND c2.isWeekday=1
AND c2.isHoliday=0
)
Thanks
> >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).
>
|
|
|
|
|
Hugo
|
Posted: Thu Dec 15 17:33:09 CST 2005 |
Top |
SQL Server Developer >> add working days to a date
>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.
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.
>
>SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWeekday = 1
> AND c.isHoliday =0
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> AND c2.dt <= c.dt
> AND c2.isWeekday=1
> AND c2.isHoliday=0
> )
To speed this up, try this modification:
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWeekday = 1
AND c.isHoliday =0
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
AND c2.dt <= c.dt
AND c2.isWeekday=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.isWeekday = 1
AND c.isHoliday =0
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
AND c2.dt <= c.dt
AND c2.isWeekday=1
AND c2.isHoliday=0
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
|
|
|
|
|
Newbie
|
Posted: Fri Dec 16 02:33:51 CST 2005 |
Top |
SQL Server Developer >> add working days to a date
Brilliant! It now takes less than 1 second when using the second mod
Thanks
>
>>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.
>
> 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.
>
>>
>>SELECT c.dt
>> FROM dbo.Calendar c
>> WHERE
>> c.isWeekday = 1
>> AND c.isHoliday =0
>> AND 9 = (
>> SELECT COUNT(*)
>> FROM dbo.Calendar c2
>> AND c2.dt <= c.dt
>> AND c2.isWeekday=1
>> AND c2.isHoliday=0
>> )
>
> To speed this up, try this modification:
>
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWeekday = 1
> AND c.isHoliday =0
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> AND c2.dt <= c.dt
> AND c2.isWeekday=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.isWeekday = 1
> AND c.isHoliday =0
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> AND c2.dt <= c.dt
> AND c2.isWeekday=1
> AND c2.isHoliday=0
> )
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|
|
|
|
|
William
|
Posted: Fri Dec 16 09:07:03 CST 2005 |
Top |
SQL Server Developer >> add working days to a date
This is a multi-part message in MIME format.
------=_NextPart_000_000B_01C60228.76937340
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Here is another fun little thing you can do with the lib. Print a =
calendar of any month(s). This basically works by enumerating the =
Calendar weeks for any month using GetMonthCalendarWeeks as shown in =
code below:
Output
----------------------------------------
January 2006
SU MO TU WE TH FR SA
01 02 03 04 05 06 07=20
08 09 10 11 12 13 14=20
15 16 17 18 19 20 21=20
22 23 24 25 26 27 28=20
29 30 31 =20
February 2006
SU MO TU WE TH FR SA
01 02 03 04=20
05 06 07 08 09 10 11=20
12 13 14 15 16 17 18=20
19 20 21 22 23 24 25=20
26 27 28 =20
March 2006
SU MO TU WE TH FR SA
01 02 03 04=20
05 06 07 08 09 10 11=20
12 13 14 15 16 17 18=20
19 20 21 22 23 24 25=20
26 27 28 29 30 31 =20
...
// 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 =3D 1; i <=3D 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 =3D DateRange.GetMonthCalendarWeeks(date, =
false);
for (int i =3D 0; i < cal.Length; i++)
{
DateRange day =3D cal[i];
if (day =3D=3D null)
Console.Write(" " + " ");
else
=
Console.Write(day.StartDate.Day.ToString().PadLeft(2, '0') + " ");
if ((i !=3D 0) && ((i + 1) % 7 =3D=3D 0))
Console.WriteLine();
}
}
--=20
William Stacey [MVP]
------=_NextPart_000_000B_01C60228.76937340
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff background=3D"">
<DIV><FONT face=3D"Lucida Console" size=3D2>Here is another fun little =
thing you can=20
do with the lib. Print a calendar of any month(s). This basically =
works by=20
enumerating the Calendar weeks for any month using <FONT=20
face=3DArial>GetMonthCalendarWeeks as shown in code =
below:</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Output</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>----------------------------------------</FONT></DIV>
<DIV><FONT face=3D"Lucida Console" size=3D2>January 2006<BR>SU MO TU WE =
TH FR=20
SA<BR>01 02 03 04 05 06 07 <BR>08 09 10 11 12 13 14 <BR>15 16 17 18 19 =
20 21=20
<BR>22 23 24 25 26 27 28 <BR>29 30=20
31  =
;=20
</FONT></DIV>
<DIV><FONT face=3D"Lucida Console" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Lucida Console" size=3D2>February 2006<BR>SU MO TU WE =
TH FR=20
SA<BR> 01 02 03 04 =
<BR>05 06 07=20
08 09 10 11 <BR>12 13 14 15 16 17 18 <BR>19 20 21 22 23 24 25 <BR>26 27=20
28  =
;=20
</FONT></DIV>
<DIV><FONT face=3D"Lucida Console" size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3D"Lucida Console">March =
2006<BR>SU MO TU=20
WE TH FR SA<BR> 01 02 03 =
04=20
<BR>05 06 07 08 09 10 11 <BR>12 13 14 15 16 17 18 <BR>19 20 21 22 23 24 =
25=20
<BR>26 27 28 29 30 31</FONT> <BR>...<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>// Put in windows or console app. =
Reference=20
the sqlutils.dll and run.<BR> =
private=20
void button2_Click(object sender, EventArgs=20
e)<BR> =20
{<BR> =20
Console.WriteLine();<BR> &=
nbsp; =20
for (int i =3D 1; i <=3D 12;=20
i++)<BR>  =
;=20
{<BR> &n=
bsp; =20
PrintMonthCalendar(new DateTime(2006, i,=20
1));<BR>  =
; =20
Console.WriteLine();<BR> &=
nbsp; =20
}<BR> =20
}<BR><BR> private void=20
PrintMonthCalendar(DateTime =
date)<BR> =20
{<BR> =20
Console.WriteLine(date.ToString("MMMM=20
yyyy"));<BR> &=
nbsp;=20
Console.WriteLine("SU MO TU WE TH FR=20
SA");<BR> &nbs=
p;=20
DateRange[] cal =3D DateRange.GetMonthCalendarWeeks(date,=20
false);<BR> &n=
bsp;=20
for (int i =3D 0; i < cal.Length;=20
i++)<BR>  =
;=20
{<BR> &n=
bsp; =20
DateRange day =3D=20
cal[i];<BR> &n=
bsp; =20
if (day =3D=3D=20
null)<BR> &nbs=
p; =20
Console.Write(" " + "=20
");<BR> =
=20
else<BR>  =
; =20
Console.Write(day.StartDate.Day.ToString().PadLeft(2, '0') + "=20
");<BR> =
=20
if ((i !=3D 0) && ((i + 1) % 7 =3D=3D=20
0))<BR> =
=20
Console.WriteLine();<BR> &=
nbsp; =20
}<BR> }<BR><BR>-- <BR>William =
Stacey=20
[MVP]<BR> </FONT></DIV></BODY></HTML>
------=_NextPart_000_000B_01C60228.76937340--
|
|
|
|
|
|
|