SQL Server Forums
Profile | Register | 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
 New Topic  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

3744 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
52325 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
52325 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000