December 23, 2008 at 9:49 am
Hello,
Is there a way to add a number of Business Days (not including Saturday and Sunday) to a date?
Users fill out a job request form. The Requested Date is just captured by GetDate(). I would like to add a Due Date that is X Business Days from the Requested Date.
Thanks,
Marc
December 23, 2008 at 10:49 am
There is no built-in method to handle this. Something like this would probably work, but would not handle holidays. The best way would use a dates table.
[font="Courier New"]-- populate temp numbers table
SELECT TOP 10
IDENTITY(INT, 1,1) AS n
INTO
#nums
FROM
sys.all_objects
SELECT
MIN(DATEADD(DAY, n, GETDATE()))
FROM
#nums
WHERE
n BETWEEN 5 AND 7 AND
CASE
WHEN DATENAME(dw, DATEADD(DAY, n, GETDATE())) IN ('Saturday', 'Sunday') THEN 0
ELSE 1
END = 1
DROP TABLE #nums
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2008 at 11:00 am
Thanks Jack!
This will work perfectly for me.
Thanks again.
Marc
December 23, 2008 at 12:43 pm
Jack Corbett (12/23/2008)
There is no built-in method to handle this. Something like this would probably work, but would not handle holidays. The best way would use a dates table.
[font="Courier New"]-- populate temp numbers table
SELECT TOP 10
IDENTITY(INT, 1,1) AS n
INTO
#nums
FROM
sys.all_objects
SELECT
MIN(DATEADD(DAY, n, GETDATE()))
FROM
#nums
WHERE
n BETWEEN 5 AND 7 AND
CASE
WHEN DATENAME(dw, DATEADD(DAY, n, GETDATE())) IN ('Saturday', 'Sunday') THEN 0
ELSE 1
END = 1
DROP TABLE #nums
[/font]
Sneaky... 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2008 at 12:50 pm
Well, now that I play with this, it's not doing what I need. Or, maybe I don't understand the code properly.
Is the line "n BETWEEN 5 AND 7" supposed to give me 5 Business Days? If so, that should be December 30th. But running the code gives me a result of Dec 29th.
Changing it to read "n BETWEEN 4 AND 6" or "n BETWEEN 6 AND 8" yields the same result.
Sorry for the ignorance!
Marc
December 23, 2008 at 1:37 pm
Okay the issue with my code is that I am including Requested Date as one of the business days. The Between 5 and 7 basically says I need to go out 5 to 7 days to the first day that is not on a weekend, since there are 2 non-business days in a 7 day week.
By not including the Requested Date in the business days you basically just need to add 7 to any weekday date since you will always hit the weekend as part of the 5 days, add 6 for Saturday, and 5 for Sunday.
It obviously gets more complex if you want to count holidays as non-business days and you need a dates table to know when they are anyway.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2008 at 2:19 pm
Here is a small little modification to Jack's code which will give you what you want.
-- populate temp numbers table
SELECT TOP 100
IDENTITY(INT, 1,1) AS n
INTO
#nums
FROM
sys.all_objects;
;WITH dates (n, datecolumn)
AS (SELECT ROW_NUMBER() OVER(ORDER BY n)
,DATEADD(DAY, n, GETDATE())
FROM #nums
WHERE 1 = CASE WHEN DATENAME(dw, DATEADD(DAY, n, GETDATE())) IN ('Saturday', 'Sunday')
THEN 0
ELSE 1
END)
SELECT datecolumn
FROM dates
WHERE n = 10; -- date 10 business days in future
DROP TABLE #nums;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2008 at 2:31 pm
Thanks Jack and Jeffrey for all your help!
I believe this is what I need.
Happy Holidays,
Marc
December 23, 2008 at 3:54 pm
And here is the above cleaned up a little:
Declare @days int;
Declare @inputDate datetime;
Set @days = 10;
Set @inputDate = '20081225';
;With nums(n)
As (Select Top(@days + (@days / 5 + 1) * 2) -- make sure we have enough days
row_number() over(Order By ao.[name]) As n
From sys.all_objects ao
)
,dates(rk ,datecolumn)
As (Select row_number() over(Order By n)
,dateadd(day, n, @inputDate)
From nums
Where datename(dw, dateadd(day, n, @inputDate)) Not In ('Saturday', 'Sunday')
)
Select datecolumn
From dates
Where rk = @days; -- date 10 business days in future
Removed the temp table and moved it into a CTE (which can easily be replaced by a numbers table). Also removed the case statement in the where clause because it really wasn't needed. Added the calculation to the TOP so we only return as many rows as are needed - well, maybe a couple extra rows 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2008 at 5:21 pm
Heh... dunno why we're pussy footin' around it... :hehe: if you don't already have a Tally table, now would be a good time to build one. To find out how to build one and how it can be used instead of loops, please see the following article.
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]
Then, the code becomes even simpler...
DECLARE @SomeDate DATETIME,
@BusinessDays INT
SELECT @SomeDate = GETDATE(),
@BusinessDays = 5
;WITH
cteAddBusinessDay AS
(
SELECT t.N+@SomeDate AS SomeDate,
ROW_NUMBER() OVER (ORDER BY t.N+@SomeDate) AS Businessday
FROM dbo.Tally t
WHERE t.N <= @BusinessDays + 7
AND DATEDIFF(dd,0,t.N+@SomeDate)%7 NOT IN (5,6)
)
SELECT SomeDate
FROM cteAddBusinessDay
WHERE Businessday = @BusinessDays
AND STILL, Jack Corbett is correct... if you need to do this a lot, you really need to build a Calendar table with the correct offset.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2008 at 5:24 pm
Here's a method of calculating the Nth working day in the future without using a CTE or tally table.
DECLARE @days int
SELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
DECLARE @weekDay int
SELECT @weekDay = DATEDIFF(day, 0, @inputDate) % 7
/* @weekDay = 0 (Monday), 2 (Tuesday), ... , 5 (Saturday), 6 (Sunday) */
DECLARE @dayOffset int
SELECT @dayOffset = CASE @weekDay
WHEN 6 THEN @days + (@days / 5) * 2 /* Sunday */
WHEN 5 THEN 1 + @days + (@days / 5) * 2 /* Saturday */
ELSE @days + ((@days + @weekDay) / 5) * 2 END
SELECT DATEADD(day, @dayOffset, @inputDate)
The method only works if [font="Courier New"]@days > 0[/font].
If [font="Courier New"]@inputDate[/font] is guaranteed to be a working day (neither Saturday nor Sunday), then the expression simplifies to:
DECLARE @days int
SELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
SELECT DATEADD(day, @days + ((@days + DATEDIFF(day, 0, @inputDate) % 7 ) / 5) * 2, @inputDate)
December 23, 2008 at 7:15 pm
andrewd.smith (12/23/2008)
Here's a method of calculating the Nth working day in the future without using a CTE or tally table.
DECLARE @days intSELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
DECLARE @weekDay int
SELECT @weekDay = DATEDIFF(day, 0, @inputDate) % 7
/* @weekDay = 0 (Monday), 2 (Tuesday), ... , 5 (Saturday), 6 (Sunday) */
DECLARE @dayOffset int
SELECT @dayOffset = CASE @weekDay
WHEN 6 THEN @days + (@days / 5) * 2 /* Sunday */
WHEN 5 THEN 1 + @days + (@days / 5) * 2 /* Saturday */
ELSE @days + ((@days + @weekDay) / 5) * 2 END
SELECT DATEADD(day, @dayOffset, @inputDate)
The method only works if [font="Courier New"]@days > 0[/font].
If [font="Courier New"]@inputDate[/font] is guaranteed to be a working day (neither Saturday nor Sunday), then the expression simplifies to:
DECLARE @days intSELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
SELECT DATEADD(day, @days + ((@days + DATEDIFF(day, 0, @inputDate) % 7 ) / 5) * 2, @inputDate)
Very cool... I've gotta look around and see if I can find Serqiy's formula for this... I never did test it but it looked a bit like this one.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2008 at 8:06 pm
Jeff Moden (12/23/2008)
andrewd.smith (12/23/2008)
Here's a method of calculating the Nth working day in the future without using a CTE or tally table.
DECLARE @days intSELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
DECLARE @weekDay int
SELECT @weekDay = DATEDIFF(day, 0, @inputDate) % 7
/* @weekDay = 0 (Monday), 2 (Tuesday), ... , 5 (Saturday), 6 (Sunday) */
DECLARE @dayOffset int
SELECT @dayOffset = CASE @weekDay
WHEN 6 THEN @days + (@days / 5) * 2 /* Sunday */
WHEN 5 THEN 1 + @days + (@days / 5) * 2 /* Saturday */
ELSE @days + ((@days + @weekDay) / 5) * 2 END
SELECT DATEADD(day, @dayOffset, @inputDate)
The method only works if [font="Courier New"]@days > 0[/font].
If [font="Courier New"]@inputDate[/font] is guaranteed to be a working day (neither Saturday nor Sunday), then the expression simplifies to:
DECLARE @days intSELECT @days = 10
DECLARE @inputDate datetime
SELECT @inputDate = '2008-12-23'
SELECT DATEADD(day, @days + ((@days + DATEDIFF(day, 0, @inputDate) % 7 ) / 5) * 2, @inputDate)
Very cool... I've gotta look around and see if I can find Serqiy's formula for this... I never did test it but it looked a bit like this one.
This is what and love and hate about making the first post, it's almost never the best one.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 27, 2008 at 1:34 pm
Marc (12/23/2008)
Thanks Jack and Jeffrey for all your help!I believe this is what I need.
Happy Holidays,
Marc
Are you sure? As mentioned before by others, you probably need a calendar table that stores the holidays.
When you described what you need, you mentioned that users fill out requests for business days. I am pretty sure that in the past few days at least one non-business day fell between Monday and Friday.
I would reconsider a calender table if I were you.
You can then do a simple select like this:
SELECT MIN(calen_dt) FROM dbo.Calender WHERE calen_dt >= DATEADD(d, @intErval, @dteCurrentDate) AND flg_bdate='Y'
Best Regards,
Chris Büttner
December 29, 2008 at 2:30 pm
Thanks Chris and everyone else for your insights. Lots of great info and examples for me to test.
Marc
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply