Subtract WorkingDays - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subtract WorkingDays

Author

Topic

pakko
Starting Member

6 Posts

Posted - 2009-08-28 : 12:54:47

Hello,
I have this Stored Procedure which adds WorkingDays (no Saturdays or Sundays) to the date I submit.

Now I want the same but in the other way,
I want a Stored Procedure which subtract's Workingdays.

Any help is very much appreciated!

This is the Stored Procedure which adds the WorkingDays:


/*********************************************************************************
		
	Sample Call:	
EXEC	[dbo].[SP_MILESTONEDATE_GET] '2009-08-28', 1
EXEC	[dbo].[SP_MILESTONEDATE_GET] '2009-08-28', 2
EXEC	[dbo].[SP_MILESTONEDATE_GET] '2009-08-28', 3
EXEC	[dbo].[SP_MILESTONEDATE_GET] '2009-08-28', 4
EXEC	[dbo].[SP_MILESTONEDATE_GET] '2009-08-28', 5
EXEC	[dbo].[SP_MILESTONEDATE_GET] '2009-08-28', 6
EXEC	[dbo].[SP_MILESTONEDATE_GET] '2009-08-28', 7
							
**********************************************************************************/

	
CREATE PROCEDURE [dbo].[SP_MILESTONEDATE_GET]
	@StartDate smalldatetime,
	@DaysToAdd INT
	
	AS
	
	SELECT 
	
	DATEADD(d,
	
	CASE DATEPART(dw,@StartDate) 
	WHEN 7 THEN 2 
	WHEN 1 THEN 1 
	ELSE 0 
	END
	
	,@StartDate)+(DATEPART(dw,DATEADD(d,
	
	CASE DATEPART(dw,@StartDate) 
	WHEN 7 	THEN 2 
	WHEN 1 THEN 1 
	ELSE 0 
	END
	
	,@StartDate))-2+@DaysToAdd)%5+((DATEPART(dw,DATEADD(d,
	
	CASE DATEPART(dw,@StartDate) 
	WHEN 7 	THEN 2 
	WHEN 1 	THEN 1 
	ELSE 0 
	END
	
	,@StartDate))-2+@DaysToAdd)/5)*7-(DATEPART(dw,DATEADD(d,
	
	CASE DATEPART(dw,@StartDate) 
	WHEN 7 	THEN 2 
	WHEN 1 THEN 1 
	ELSE 0 
	END
	
	,@StartDate))-2) 
	
	As ReturnDate
	
GO

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-28 : 13:53:20

SELECT DATEADD(dw,-1,GETDATE())

Like that?

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-28 : 13:58:44

You could also make your SP a lot smaller like this.

CREATE PROCEDURE [dbo].[SP_MILESTONEDATE_GET]
@StartDate smalldatetime,
@DaysToAdd INT

AS

SELECT DATEADD(dw,@daystoadd,@startdate) As ReturnDate

You can give this SP a negative number and it will subtract the days as well as add.

An infinite universe is the ultimate cartesian product.

pakko
Starting Member

6 Posts

Posted - 2009-08-28 : 14:02:03

no, I want to subtract weekdays (no saturdays or sundays),
like in the attached stored procedure, but subtracting instead of adding

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-28 : 14:57:12

Peso has extensively covered all this in his blog...you might want to check this and see if it helps.

http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx

pakko
Starting Member

6 Posts

Posted - 2009-08-28 : 14:57:42

sorry, my subject was wrong..
I mean WORKINGDAYS, not weekdays!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-28 : 15:48:52

Then you basically use the same sort of logic but use - in dateadd

But Vijay is right, you'd do well to read Peso's blog.

An infinite universe is the ultimate cartesian product.

pakko
Starting Member

6 Posts

Posted - 2009-08-29 : 02:16:54

Then you basically use the same sort of logic but use - in dateadd

I know that, but I don't know how! Please help me!

pakko
Starting Member

6 Posts

Posted - 2009-08-29 : 13:21:47

I found out that it is pretty easy with excel.

I am not able to translate it to sql. still looking for a solution, can anyone help?

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 06:11:16

quote:
Originally posted by pakko

[font=Verdana]
Hello,
I have this Stored Procedure which adds WorkingDays (no Saturdays or Sundays) to the date I submit.

quote:
Originally posted by pakko

sorry, my subject was wrong..
I mean WORKINGDAYS, not weekdays!

I think you've confused yourself? By definition a "weekday" is any day other than Saturday or Sunday. What is the difference in your example between a "WorkingDay" and a weekday?

Perhaps a "WorkingDay" calculates the new work day according to a holiday/vacation schedule for the company? In other words, if you subtract 2 working days from a given date, you only want to return actual work days; you don't want to return Labor Day (which could be a company holiday) for example. Is that what you're after?

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources