October 22, 2007 at 4:08 am
Greg Snidow (10/21/2007)
Kenneth, I am using it for setting the delivery date for email notification in an email staging table. I am experimenting with a set up suggeted by Sergiy in my post a few days ago called 'trigger help'. Anyhow, I need to be able to set a delivery date of an email based on the enter date of a record. It just so happens this person wanted 5, 10, and 20 day notification using week days only. It just as well could have been 37 weekdays, or 63, or whatever it needs to be.
So, you need to add not working days but weekdays.
Why did not you ask for it from the beginning?
😉
You know, with 5, 10 and 20 days options it's quite easy:
5 weekdays = 7 calendar days, 10 = 14, 20 = 28.
🙂
If you expect these numbers to be changed you need to follow this (let's take 12 as an example):
1) convert number of whole weeks to calendar days - 10 wd is 2 whole weeks - 14 days;
2) Add remainder to the (1) result;
3) figure out which day of the week you're in.
This will return 1 for Monday ... 7 for Sunday independently of DATEFIRST settings:
[font="Courier New"](@@Datefirst + Datepart(dw, YourDate) - 2) %7 +1[/font]
4) if the remainder (2 in our example) is > 5 - Current Weekday then add 2 days, else leave the number as it is.
If to translate it into T-SQL it's gonna be something like this:
[Code]
SELECT CreateDate + @NotificationDays%5*7 + @NotificationDays/5 +
CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays/5) > 5
THEN 2 ELSE 0 END As NotificationDate
From inserted
[/Code]
NB. Don't trust my typing! I don't have a chance to check it now, so do it yourself.
_____________
Code for TallyGenerator
October 22, 2007 at 6:48 am
Michael Meierruth (10/22/2007)
How to you select/copy/paste the sample code in the gray background areas?When I do this I don't get any line breaks.
I know... pretty bad, huh? Hope they fix that soon.
It does have "linefeed" or "newline" characters. If you copy'n'paste into MS Word and do a search'n'replace from ^l (circumflex small "L") to ^p (circumflex small "P"), you get the necessary line breaks (carriage return). Then, copy'n'paste from Word to whatever.
Leading space will still be lost, though... makes a formatting mess... but the code will work.
--Jeff Moden
October 22, 2007 at 12:41 pm
Kenneth Wilhelmsson (10/22/2007)
Greg,ok, and how are these mails staged?
one-by-one? By the thousands? (or millions?)
Are you looking for the 'batch-approach' that Jeff speaks of, or is it less?
Just trying to set the frames for where 'good enough' might be...
/Kenneth
Kenneth
I am using a newly created email table that has fields for all the parameters necessary for my mail procedure, such as ToAddress, CCAddress,Subjec,Body, and some others,including delivery_dt, and sent_dt. I am trying to build what Sergiy was talking about(or at least what I got out of it) on my 'trigger help' post a few days ago. Basically, i wrote a trigger that inserts all the information, from any table on which I put the trigger, into the email staging table using the function that is the highlight of this thread to set the delivery date. Then, every night I run a procedure to send out the ones where delivery_dt is today, and populate sent_dt. I have another update trigger on the table to delete records from the email staging table that no longer need to be sent. So in short, I will in no way be using this function for large batches, but now that I have it who knows?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2007 at 1:05 pm
Sergiy (10/22/2007)
Greg Snidow (10/21/2007)
Kenneth, I am using it for setting the delivery date for email notification in an email staging table. I am experimenting with a set up suggeted by Sergiy in my post a few days ago called 'trigger help'. Anyhow, I need to be able to set a delivery date of an email based on the enter date of a record. It just so happens this person wanted 5, 10, and 20 day notification using week days only. It just as well could have been 37 weekdays, or 63, or whatever it needs to be.So, you need to add not working days but weekdays.
Why did not you ask for it from the beginning?
😉
You know, with 5, 10 and 20 days options it's quite easy:
5 weekdays = 7 calendar days, 10 = 14, 20 = 28.
🙂
If you expect these numbers to be changed you need to follow this (let's take 12 as an example):
1) convert number of whole weeks to calendar days - 10 wd is 2 whole weeks - 14 days;
2) Add remainder to the (1) result;
3) figure out which day of the week you're in.
This will return 1 for Monday ... 7 for Sunday independently of DATEFIRST settings:
[font="Courier New"](@@Datefirst + Datepart(dw, YourDate) - 2) %7 +1[/font]
4) if the remainder (2 in our example) is > 5 - Current Weekday then add 2 days, else leave the number as it is.
If to translate it into T-SQL it's gonna be something like this:
[Code]
SELECT CreateDate + @NotificationDays%5*7 + @NotificationDays/5 +
CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays/5) > 5
THEN 2 ELSE 0 END As NotificationDate
From inserted
[/Code]
NB. Don't trust my typing! I don't have a chance to check it now, so do it yourself.
Sergiy
I did clarify in the beginning that I should have referred to them as weekdays in the original post. Anyhow, thank you for the explanation of how it works. I can't get my head around what everyone's excellent solutions are doing with the modulo, but I think I am starting to get it.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2007 at 1:06 pm
Michael Meierruth (10/22/2007)
How to you select/copy/paste the sample code in the gray background areas?When I do this I don't get any line breaks.
When in the eidtor window - pick the CODE IFCODE, and paste your code between the two tags.
Still won't paginate it, but that's what puts the gray background in.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 22, 2007 at 1:11 pm
Of course I messed with whole number divisions and remainders.
Here is correct version (I believe ;))
[Code]
SELECT CreateDate + @NotificationDays/5*7 + @NotificationDays%5 +
CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays%5) > 5
THEN 2 ELSE 0 END As NotificationDate
From inserted
[/Code]
_____________
Code for TallyGenerator
October 22, 2007 at 1:58 pm
Matt Miller (10/22/2007)
When in the eidtor window - pick the CODE IFCODE, and paste your code between the two tags.
Still won't paginate it, but that's what puts the gray background in.
Matt,
I wans't trying to get code into a post but rather out of a post. Jeff's suggestion of going via MS Word works just fine.
October 22, 2007 at 6:59 pm
Sergiy (10/22/2007)
Of course I messed with whole number divisions and remainders.Here is correct version (I believe ;))
[Code]
SELECT CreateDate + @NotificationDays/5*7 + @NotificationDays%5 +
CASE WHEN (@@Datefirst + Datepart(dw, CreateDate) - 2) %7 +1 + @NotificationDays%5) > 5
THEN 2 ELSE 0 END As NotificationDate
From inserted
[/Code]
Sergiy, I gave it the same try I did with the others, and something seems to be amiss. I should never have @End be a weekend. I am still trying to understand it, just wanted to let you know something's up with it. Thank you for your help.
IF OBJECT_ID('dbo.fnAddWeekdays_Sergiy','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWeekdays_Sergiy
GO
CREATE FUNCTION dbo.fnAddWeekdays_Sergiy (@Start DATETIME, @Days INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @End DATETIME
SELECT @End = @Start + @Days/5*7 + @Days%5 +
CASE WHEN (@@DATEFIRST + (DATEPART(dw, @Start) - 2) % 7 + 1 + @Days%5) > 5 THEN 2
ELSE 0 END
RETURN @End
END
GO
IF OBJECT_ID('TempDB..#Test','u') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test
(
StartDate SMALLDATETIME
)
INSERT INTO #Test
SELECT '1/1/2008' UNION ALL
SELECT '1/2/2008' UNION ALL
SELECT '1/3/2008' UNION ALL
SELECT '1/4/2008' UNION ALL
SELECT '1/5/2008' UNION ALL
SELECT '1/6/2008' UNION ALL
SELECT '1/7/2008' UNION ALL
SELECT '1/8/2008' UNION ALL
SELECT '1/9/2008' UNION ALL
SELECT '1/10/2008' UNION ALL
SELECT '1/11/2008' UNION ALL
SELECT '1/12/2008' UNION ALL
SELECT '1/13/2008' UNION ALL
SELECT '1/14/2008' UNION ALL
SELECT '1/15/2008' UNION ALL
SELECT '1/16/2008' UNION ALL
SELECT '1/17/2008' UNION ALL
SELECT '1/18/2008' UNION ALL
SELECT '1/19/2008' UNION ALL
SELECT '1/20/2008' UNION ALL
SELECT '1/21/2008' UNION ALL
SELECT '1/22/2008' UNION ALL
SELECT '1/23/2008' UNION ALL
SELECT '1/24/2008' UNION ALL
SELECT '1/25/2008' UNION ALL
SELECT '1/26/2008' UNION ALL
SELECT '1/27/2008' UNION ALL
SELECT '1/28/2008' UNION ALL
SELECT '1/29/2008' UNION ALL
SELECT '1/30/2008' UNION ALL
SELECT '1/31/2008'
SELECT
DATENAME(dw,StartDate),
DATENAME(dw,dbo.fnWorkdays_Sergiy(startdate,5))
FROM #Test
DROP TABLE #Test
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2007 at 7:39 pm
I said - I could not test.
There was a typo in the formula.
Also I corrected small logical error in the code:
[Code]
IF OBJECT_ID('dbo.fnAddWeekdays_Sergiy','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWeekdays_Sergiy
GO
CREATE FUNCTION dbo.fnAddWeekdays_Sergiy (@Start DATETIME, @Days INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @End DATETIME
SELECT @End = @Start + @Days/5*7 + @Days%5 +
CASE WHEN (@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5 > 5
THEN 8 - ((@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5)
ELSE 0 END
RETURN @End
END
[/Code]
Seems all right now.
_____________
Code for TallyGenerator
October 22, 2007 at 8:08 pm
Sergiy, wouldn't it be possible to have a small table of only hollidays, and if @DateEnd falls on one of those days just add 1?
Here are a couple of functions I use. One tells you the number of working (week) days between two dates without using a date tally table and the other lets you add/subtract working days to/from a date and gives you the resultant date.
The nice thing is that, although commented out because it is not yet implemented, they will work with a table of defined holidays. This is a table of defined holidays that you or your company or your customer will be observing into the foreseeable future (which is as far ahead as you wish to define it). It will also not double-count holidays that happen to occur on a weekend.:cool:
I haven't tested these on million-row tables but I would be interested in the results if anyone wants to make the effort,:P but these routines were developed more with an eye toward accuracy rather than performance.
USE [Utility]
GO
/****** Object: UserDefinedFunction [dbo].[BizDaysBetween] Script Date: 10/22/2007 18:54:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
=============================================
Author:
Tomm Carr
Create date:
06/07/2007
Description:
Returns the number of week days that occur between two dates.
Notes:
* As of the development date above, Business is still debating whether or not to use
the table of company holidays in calculating the number of work days between two dates.
Should they decide to use this table, performance will be effected accordingly.
=============================================
*/
ALTER FUNCTION [dbo].[BizDaysBetween] (
@StartDate datetime,
@EndDate dateTime
)
returns int
as
begin
declare @Result int,
@TmpDate datetime;
-- We don't want to be picky about whether or not the starting date actually comes before
-- the end date as passed to this function. So we check here and swap them if necessary.
if @StartDate > @EndDate
begin
set @TmpDate = @StartDate;
set @StartDate = @EndDate;
set @EndDate = @TmpDate;
end--if
--If starting on a weekend, set to the next business day
if DatePart( dw, @StartDate ) = 1 -- Sunday
set @StartDate = @StartDate + 1;
else if DatePart( dw, @StartDate ) = 7 -- Saturday
set @StartDate = @StartDate + 2;
--If ending on a weekend, set to the last business day
if DatePart( dw, @EndDate ) = 1 -- Sunday
set @EndDate = @EndDate - 2;
else if DatePart( dw, @EndDate ) = 7 -- Saturday
set @EndDate = @EndDate - 1;
select @Result = DateDiff( d, @StartDate, @EndDate )
- (DateDiff( wk, @StartDate, @EndDate ) * 2 )
-- Uncomment the following section to implement using company holidays in the calculation
-- - ( SELECT COUNT(*)
-- FROM dbo.Holiday
-- WHERE HolidayDate BETWEEN @StartDate AND @EndDate
-- AND DATEPART(dw,HolidayDate) BETWEEN 2 AND 6
-- )
-- If the two dates happen to be the Saturday and Sunday of the same weekend, the result
-- so far will be -1. As this is not a valid result for this function, turn that value into
-- a zero. After all, there are no business days between Saturday and Sunday.
return case when @Result < 0 then 0 else @Result end;
end--function
The other:
USE [Utility]
GO
/****** Object: UserDefinedFunction [dbo].[BizDaysAdd] Script Date: 10/22/2007 18:59:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Created by:
Tomm Carr
Date:
9/25/2007
Description:
This function... um... functions much like DateAdd except that it only works with days
and it adds (or subtracts) *business days* to/from the specified date.
*/
ALTER FUNCTION [dbo].[BizDaysAdd] (
@StartDate DateTime,
@Offset int
)
returns DateTime
as
begin
declare @Result Datetime,
@TmpOffset int;
-- First, just go ahead and do a rough DateAdd approximation
set @Result = DateAdd( dd, @Offset + (@Offset * 2 / 7), @StartDate );
-- Then find the difference between our estimate and the actual count. However,
-- the calculation is slightly different if the offset is negative.
if @Offset > 0
set @TmpOffset = @Offset - dbo.BizDaysBetween( @StartDate, @Result );
else
set @TmpOffset = @Offset + dbo.BizDaysBetween( @StartDate, @Result );
-- Now "walk" to the correct date. The loop is actually pretty fast and will take from
-- 0 iterations (if we hit the answer the first time -- not likely) up to 4 or 5 for offset
-- values representing 1000 years or more. Generally the number of iterations for most
-- values will be 1 or 2.
while @TmpOffset <> 0
begin
set @Result = DateAdd( dd, @TmpOffset + (@TmpOffset * 2 / 7), @Result );
if @TmpOffset > 0
set @TmpOffset = @Offset - dbo.BizDaysBetween( @StartDate, @Result );
else
set @TmpOffset = @Offset + dbo.BizDaysBetween( @StartDate, @Result );
end--while
return @Result;
end--Function
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 22, 2007 at 8:48 pm
if DatePart( dw, @StartDate ) = 1 -- Sunday
On my server it means Monday, not Sunday.
_____________
Code for TallyGenerator
October 23, 2007 at 4:41 am
Sergiy (10/22/2007)
if DatePart( dw, @StartDate ) = 1 -- SundayOn my server it means Monday, not Sunday.
I think that's why Jefff uses the function 'datename' which is always correct independent of 'set datefirst'.
October 23, 2007 at 5:16 am
try this SP and implement accordingly....
http://www.codeproject.com/cs/library/datetimelib.asp?print=true
October 23, 2007 at 5:56 am
Michael Meierruth (10/23/2007)
Sergiy (10/22/2007)
if DatePart( dw, @StartDate ) = 1 -- SundayOn my server it means Monday, not Sunday.
I think that's why Jefff uses the function 'datename' which is always correct independent of 'set datefirst'.
Jeff's function fails if you've got set, say, German language.
_____________
Code for TallyGenerator
October 23, 2007 at 7:16 am
... or Swedish... or any other non-english language where days are spelled differently 😉
If you want to know which day of the week a given date is, then there is exactly one way that will always give the correct answer, regardless of DATEFIRST settings, or language settings.
It's the way Sergiy wrote before:
-- quote --
This will return 1 for Monday ... 7 for Sunday independently of DATEFIRST settings:
(@@Datefirst + Datepart(dw, YourDate) - 2) %7 +1
-- end quote --
Just as there is exactly one way to denote datestrings that's also independent of DATEFORMAT or language: 'SSYYMMDD'
/Kenneth
Viewing 15 posts - 61 through 75 (of 117 total)
You must be logged in to reply to this topic. Login to reply