Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Adding workdays Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2007 2:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #411918
Posted Wednesday, October 17, 2007 4:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:23 PM
Points: 199, Visits: 590
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



Post #411960
Posted Wednesday, October 17, 2007 5:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
Ok, I figured it out. I knew it had to be a reverse of Jeff's code, which can be found at

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.
Post #411980
Posted Wednesday, October 17, 2007 6:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #411984
Posted Wednesday, October 17, 2007 7:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #411994
Posted Wednesday, October 17, 2007 7:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 09, 2014 3:33 AM
Points: 1,559, Visits: 672
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
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?
Post #412198
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse