Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Adding workdays Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, October 17, 2007 2:13 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 11:24 AM Points: 1,579, Visits: 2,475
 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.fnAddWorkdaysGOCREATE FUNCTION dbo.fnAddWorkdays (@Startdate DATETIME,@NumberDays INT)RETURNS DATETIMEASBEGIN 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 @EnddateENDSELECT 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.
Post #411918
 Posted Wednesday, October 17, 2007 4:31 PM
 SSC Veteran Group: General Forum Members Last Login: Friday, December 2, 2016 7:12 AM Points: 204, Visits: 682
 This probably isn't the most efficient way but...Create a date table (reasonable range)weekdays (id, dayDate)with only weekday dates.FROM invcinner join weekdays currDate on invc.mydate = currDate.dayDate --may need both at midnightinner join weekdays datePlus5 on currDate.id + 5 = datePlus5.idThe first inner join may need a caveat if invoices can come in on weekends....daryl
Post #411960
 Posted Wednesday, October 17, 2007 5:23 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 11:24 AM Points: 1,579, Visits: 2,475
 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.
Post #411972
 Posted Wednesday, October 17, 2007 6:18 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 11:24 AM Points: 1,579, Visits: 2,475
 Ok, I figured it out. I knew it had to be a reverse of Jeff's code, which can be found atwww.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 thisIF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL DROP FUNCTION dbo.fnAddWorkdaysGOCREATE FUNCTION dbo.fnAddWorkdays (@Start DATETIME,@NumDays INT)RETURNS DATETIMEASBEGIN 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 @EnddateENDAnd I can test it against Jeff's codeSELECT 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 20DECLARE @Start DATETIME, @End DATETIMESELECT @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.
Post #411980
 Posted Wednesday, October 17, 2007 6:56 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 11:24 AM Points: 1,579, Visits: 2,475
 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 #TestCREATE TABLE #Test(Start SMALLDATETIME)INSERT INTO #TestSELECT '1/1/2007' UNION ALLSELECT '1/2/2007' UNION ALLSELECT '1/3/2007' UNION ALLSELECT '1/4/2007' UNION ALLSELECT '1/5/2007' UNION ALLSELECT '1/6/2007' UNION ALLSELECT '1/7/2007' UNION ALLSELECT '1/8/2007' UNION ALLSELECT '1/9/2007' UNION ALLSELECT '1/10/2007' UNION ALLSELECT '1/11/2007' UNION ALLSELECT '1/12/2007' UNION ALLSELECT '1/13/2007' UNION ALLSELECT '1/14/2007' UNION ALLSELECT '1/15/2007' UNION ALLSELECT '1/16/2007' UNION ALLSELECT '1/17/2007' UNION ALLSELECT '1/18/2007' UNION ALLSELECT '1/19/2007' UNION ALLSELECT '1/20/2007' UNION ALLSELECT '1/21/2007' UNION ALLSELECT '1/22/2007' UNION ALLSELECT '1/23/2007' UNION ALLSELECT '1/24/2007' UNION ALLSELECT '1/25/2007' UNION ALLSELECT '1/26/2007' UNION ALLSELECT '1/27/2007' UNION ALLSELECT '1/28/2007' UNION ALLSELECT '1/29/2007' UNION ALLSELECT '1/30/2007' UNION ALLSELECT '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.
Post #411984
 Posted Wednesday, October 17, 2007 7:32 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 11:24 AM Points: 1,579, Visits: 2,475
 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.fnAddWorkdaysGOCREATE FUNCTION dbo.fnAddWorkdays (@Start DATETIME,@NumDays INT)RETURNS DATETIMEASBEGIN 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 @EnddateEND Greg_________________________________________________________________________________________________The glass is at one half capacity: nothing more, nothing less.
Post #411994
 Posted Wednesday, October 17, 2007 7:36 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 11:24 AM Points: 1,579, Visits: 2,475
 False alarm. There is still something wrong with saturdays. Greg_________________________________________________________________________________________________The glass is at one half capacity: nothing more, nothing less.
Post #411996
 Posted Thursday, October 18, 2007 3:26 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, May 24, 2016 2:15 AM Points: 1,559, Visits: 699
 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
Post #412107
 Posted Thursday, October 18, 2007 6:11 AM
 SSCommitted Group: General Forum Members Last Login: Wednesday, November 30, 2016 11:24 AM Points: 1,579, Visits: 2,475
 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.
Post #412168
 Posted Thursday, October 18, 2007 7:22 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:09 PM Points: 7,507, Visits: 17,958