October 17, 2007 at 2:13 pm
Sergiy, this goes with my last trigger post, but this is kind of a new topic. I made a test table for emails with a column for delivery_dt as you suggested. I thought all was well, but then I realized I did not know how to add say 5 workdays. Using Jeffs marvelous code for calculating the number of workdays between two dates helped me know how many work days had passed given a startdate, but now I need to be able to set the delivery_dt = startdate plus 5 workdays. I thought it would be easy, but I am completely stumped. I started by making a UDF like so...
IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWorkdays
GO
CREATE FUNCTION dbo.fnAddWorkdays (@Startdate DATETIME,@NumberDays INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @EndDate DATETIME
SELECT @EndDate = DATEADD(d,@NumberDays,@Startdate)
SELECT @EndDate = DATEADD(d,
CASE WHEN DATENAME(dw,@Enddate) = 'Saturday' THEN 2
WHEN DATENAME(dw,@Enddate) = 'Sunday' THEN 1
ELSE 0
END,@EndDate)
RETURN @Enddate
END
SELECT dbo.fnAddWorkdays(GETDATE(),5)
I quickly realized, though, that it was not going to be that easy. My function only adds 1 or 2 if the enddate is saturday or sunday. It does not account for the weekend days in between. So, anyone have any ideas?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 17, 2007 at 4:31 pm
This probably isn't the most efficient way but...
Create a date table (reasonable range)
weekdays (id, dayDate)
with only weekday dates.
FROM invc
inner join weekdays currDate on invc.mydate = currDate.dayDate --may need both at midnight
inner join weekdays datePlus5 on currDate.id + 5 = datePlus5.id
The first inner join may need a caveat if invoices can come in on weekends....
daryl
October 17, 2007 at 5:23 pm
Thanks Daryl. I want to do it without having to make a days table. I know there is a way, I just can't get my head around it
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 17, 2007 at 6:18 pm
Ok, I figured it out. I knew it had to be a reverse of Jeff's code, which can be found at
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/.
And it was. I had to read the article several times to really understand what was happening, but basically what I needed to do is this
IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWorkdays
GO
CREATE FUNCTION dbo.fnAddWorkdays (@Start DATETIME,@NumDays INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @EndDate DATETIME
SELECT @EndDate =
DATEADD(d,@NumDays,@Start) + (DATEDIFF(wk,@Start,DATEADD(d,@NumDays,@Start))*2)
+(CASE WHEN DATENAME(dw,@Start) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw,@Start) = 'Saturday' THEN 1 ELSE 0 END)
RETURN @Enddate
END
And I can test it against Jeff's code
SELECT dbo.fnAddWorkdays('1/1/2007',20) returns 1/27/2007. If I use 1/27/2007 as the end date I can run Jeff's code to get 20
DECLARE
@Start DATETIME,
@End DATETIME
SELECT @Start = '1/1/2007'
SELECT @End = '1/27/2007'
SELECT
(DATEDIFF(dd, @Start, @End) + 1)-(DATEDIFF(wk, @Start, @End) * 2)
-(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @End) = 'Saturday' THEN 1 ELSE 0 END)
Thanks again Jeff. Just another example of how you continue to teach me without even knowing it.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 17, 2007 at 6:56 pm
Well...it seems to have some problems. If I start on a week day I should always end on a week day, but my function does not seem to do that. See for yourself. I guess its back to the drawing board.
IF OBJECT_ID('TempDB..#Test','u') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test
(
Start SMALLDATETIME
)
INSERT INTO #Test
SELECT '1/1/2007' UNION ALL
SELECT '1/2/2007' UNION ALL
SELECT '1/3/2007' UNION ALL
SELECT '1/4/2007' UNION ALL
SELECT '1/5/2007' UNION ALL
SELECT '1/6/2007' UNION ALL
SELECT '1/7/2007' UNION ALL
SELECT '1/8/2007' UNION ALL
SELECT '1/9/2007' UNION ALL
SELECT '1/10/2007' UNION ALL
SELECT '1/11/2007' UNION ALL
SELECT '1/12/2007' UNION ALL
SELECT '1/13/2007' UNION ALL
SELECT '1/14/2007' UNION ALL
SELECT '1/15/2007' UNION ALL
SELECT '1/16/2007' UNION ALL
SELECT '1/17/2007' UNION ALL
SELECT '1/18/2007' UNION ALL
SELECT '1/19/2007' UNION ALL
SELECT '1/20/2007' UNION ALL
SELECT '1/21/2007' UNION ALL
SELECT '1/22/2007' UNION ALL
SELECT '1/23/2007' UNION ALL
SELECT '1/24/2007' UNION ALL
SELECT '1/25/2007' UNION ALL
SELECT '1/26/2007' UNION ALL
SELECT '1/27/2007' UNION ALL
SELECT '1/28/2007' UNION ALL
SELECT '1/29/2007' UNION ALL
SELECT '1/30/2007' UNION ALL
SELECT '1/31/2007'
SELECT
Start,
DayOfWeek1 = DATENAME(dw,start),
StartAdd5 = dbo.fnAddWorkdays(start,5),
DayOfWeek2 = DATENAME(dw,dbo.fnAddWorkdays(start,5))
FROM #Test
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 17, 2007 at 7:32 pm
Ok, I think I fixed it. I forgot one thing from Jeffs function, then I just fiddled with the numbers until it seemed to work.
IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWorkdays
GO
CREATE FUNCTION dbo.fnAddWorkdays (@Start DATETIME,@NumDays INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @EndDate DATETIME
SELECT @EndDate =
(DATEADD(d,@NumDays,@Start)-1) + -- I forgot the -1 here (DATEDIFF(wk,@Start,DATEADD(d,@NumDays,@Start))*2)
+(CASE WHEN DATENAME(dw,@Start) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw,@Start) = 'Saturday' THEN 3 ELSE 0 END) --changed the 1 to a 3 and it works
RETURN @Enddate
END
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 17, 2007 at 7:36 pm
False alarm. There is still something wrong with saturdays.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 3:26 am
So, what's the deal with not having a dates table?
It will solve all your problems, no need for complex functions or anything else exotic.
The main problem: How do you define 'workday' programatically?
You can't. Simple as that.
'Workday' is a business rule, it differs from place to place, so you need to single out those 'special' days anyway. Might as well do it once, store it in a permanent table for lookups, then it's always there to be found. No need to try to calculate it everytime.
Do it once, then forget about it. 🙂
/Kenneth
October 18, 2007 at 6:11 am
Kenneth, I probably should refer to it as weekdays, but as far as my production is concerned no weekends are work days. I may end up having to make a table, but I am sure what I am trying to do is possible. If Jeff's code can take a start and end date and count all the week days, that is like saying b - a = X, where a and b are both known, and a is @Start and b is @End and X is an unknown integer. Logically, all I want to do is shift it around to be a + X = b where a is @Start and X is a known integer. Do you see any reason why this would not be so? I am just not skilled enough to do it, but I will keep working on it.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 7:22 am
Try this one on:
--set up
declare @dateStart datetime
declare @daysToAdd int
declare @dateEnd datetime
declare @baseday datetime
select @datestart='9/1/2007',@daysToAdd=52
--start by making sure your start day IS a business day
Select @datestart=dateadd(dd,case when cast(cast(@datestart as datetime) as int)%7 >4 then
7-cast(cast(@datestart as datetime) as int)%7 else 0 end,@datestart)
select @datestart
--the magic
select @dateEnd=dateadd(dd,
case when cast(cast(dateadd(dd,@daystoAdd%5,@datestart) as datetime) as int)%7 >4 then
7-cast(cast(dateadd(dd,@daystoAdd%5,@datestart) as datetime) as int)%7 else 0 end,
dateadd(dd,@daystoAdd%5,dateadd(wk,@daysToAdd/5,@datestart)))
select @dateend
----------------------------------------------------------------------------------
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 18, 2007 at 7:59 am
Kenneth Wilhelmsson (10/18/2007)
So, what's the deal with not having a dates table?It will solve all your problems, no need for complex functions or anything else exotic.
The main problem: How do you define 'workday' programatically?
You can't. Simple as that.
'Workday' is a business rule, it differs from place to place, so you need to single out those 'special' days anyway. Might as well do it once, store it in a permanent table for lookups, then it's always there to be found. No need to try to calculate it everytime.
Do it once, then forget about it. 🙂
/Kenneth
I've found that date tables are fine for RBAR... but, how would you use the date table in this instance of batch code and still have some semblance of performance? Haven't seen a high performance method of using a date table for this, yet.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 8:14 am
Greg Snidow (10/18/2007)
Kenneth, I probably should refer to it as weekdays, but as far as my production is concerned no weekends are work days.
Ah, ok. That's another story then. You just want to count all days except weekdays Saturdays and Sundays. Though even that would be easy as pie with a dates table. The reason being that you then don't have to expand all dates within the range, all dates are already there, just waiting to be selected, even without the attribute 'holiday' in said dates table.
/Kenneth
October 18, 2007 at 8:20 am
I've found that date tables are fine for RBAR... but, how would you use the date table in this instance of batch code and still have some semblance of performance? Haven't seen a high performance method of using a date table for this, yet.
Hmmm... maybe you're right, Jeff. Might be trickier than I thought...
I'll ponder on it ([reminder] must read op more thoroughly.. [/reminder])
/Kenneth
October 18, 2007 at 8:36 am
Jeff Moden (10/18/2007)
Kenneth Wilhelmsson (10/18/2007)
I've found that date tables are fine for RBAR... but, how would you use the date table in this instance of batch code and still have some semblance of performance? Haven't seen a high performance method of using a date table for this, yet.
So, Jeff, do you think what I am trying to do is possible, or am I beating a dead horse? I have fiddled with it to get it work for *every* day up to 25 days, then for all time when the start date is a week day, but I am still missing something. I am going nuts, its got to be something so simple.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 8:42 am
Greg -
I think you need to try the solution I posted. The reverse engineering you did has some logic flaws. In particular - you're going to start "losing" business days when you add large numbers of business days.
I broke out the "fixing the start date" if it's not a business day, because that wouldn't be necessary if you can ensure that the start date will NEVER be a non-business day.
Now - there might be a more elegant way to do it, but I'm fairly confident it's accurate.
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 1 through 15 (of 117 total)
You must be logged in to reply to this topic. Login to reply