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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SOLVED - DateAdd subtracting Workdays
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssimon
Starting Member

USA
16 Posts

Posted - 03/12/2013 :  11:30:49  Show Profile  Visit ssimon's Homepage  Reply with Quote
Basically I have a large query, but within the query I have to do a date calculation.

pm.PURLT_01 = 33
om.CURDUE_10 = 2013-06-18 00:00:00

What I have so far:
convert(varchar,DATEADD(dw, -pm.PURLT_01 , convert(varchar, om.[CURDUE_10] , 101)), 101) AS [Start Date]

returns 5/16/2013 (which is 33 days before)

But I need to have it return 5/02/2013 (33 Workdays (M-F))

Edited by - ssimon on 03/13/2013 13:16:35

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 03/12/2013 :  13:01:44  Show Profile  Reply with Quote
There is probably a simple way to calculate this, but my mind is not cooperating at the moment. Hopefully someone else will post it soon. In the meantime, here is a roundabout way of doing it. Feel free to laugh at it :)

To use this, you need to create a calendar table first, if you don't already have one in your database. You can create one like this:

create table  #Calendar(dt datetime not null primary key);
;with cte as 
(
select CAST('20130101' as datetime) as dt
union all 
select DATEADD(dd,1,dt) from cte where dt < '20131231'
)
insert into #Calendar select * from cte
option (maxrecursion 0);
Now your query will be like shown below. I am making up a temp table just to demonstrate. In your actual query, you would use the real table instead of #tmp:
create table #tmp(PURLT_01 int , CURDUE_10 datetime);
insert into #tmp values (33,'20130618'),(3,'20130312')

select
	t.*,
	c.dt
from
	#tmp t
	inner join #Calendar c on
		DATEDIFF(dd,dt,CURDUE_10)-2*DATEDIFF(wk,dt,CURDUE_10) = PURLT_01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/12/2013 :  14:00:50  Show Profile  Reply with Quote
one method is this

DECLARE @Day int= 33,@date datetime = '2013-06-18',@startdate = DATEADD(dd,-2*@day,@date)

SELECT MIN(Date) AS StartDate
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Seq,Date,Day,WeekDay FROM dbo.CalendarTable(@startdate,@date,1,0)
)f
WHERE Seq <=@Day+1


CalendarTable can be found below

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssimon
Starting Member

USA
16 Posts

Posted - 03/12/2013 :  14:30:12  Show Profile  Visit ssimon's Homepage  Reply with Quote
Sorry guys - that is wayyyy over my head LOL

--Can you explain the statement - DATEADD(dd,-2*@day,@date)
to me this is saying multiple 33 by 2 (as a negative number) , then remove 66 days (dd) from 6/18/2013.

Is there anyway just to keep this within my query? (sorry I only know queries and not much on SP / Views / functions, etc..)
Go to Top of Page

ssimon
Starting Member

USA
16 Posts

Posted - 03/13/2013 :  11:37:00  Show Profile  Visit ssimon's Homepage  Reply with Quote
I do have a table that has calendar dates (Shop_Calendar) that contains two columns DateValue (smalldatetime starting at 1989-12-31 and running through 2078-12-31) and ShopDay (bit 1=Workday, 0=Non-Workday which includes holidays and weekends)


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/13/2013 :  11:44:53  Show Profile  Reply with Quote
then you can just do

DECLARE @Day int= 33,@date datetime = '2013-06-18'

SELECT MIN(DateValue) AS MinDate
FROM
(
SELECT TOP (@Day) DateValue
FROM Shop_Calendar
WHERE DateValue < = @date
AND ShopDay=1
ORDER BY DateValue DESC
)r


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssimon
Starting Member

USA
16 Posts

Posted - 03/13/2013 :  13:16:14  Show Profile  Visit ssimon's Homepage  Reply with Quote
Visakh,

Thank you

,convert(varchar,(
SELECT MIN(DateValue) AS MinDate FROM (SELECT TOP (pm.PURLT_01) DateValue
FROM Shop_Calendar WHERE DateValue < = om.[CURDUE_10]
AND ShopDay=1 ORDER BY DateValue DESC )r), 101) [Start Date]

Worked like a charm!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000