SOLVED - DateAdd subtracting Workdays

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

General SQL Server Forums

New to SQL Server Programming

SOLVED - DateAdd subtracting Workdays

Author

Topic

ssimon
Starting Member

16 Posts

Posted - 2013-03-12 : 11:30:49

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))

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-12 : 13:01:44

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 14:00:50

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/

ssimon
Starting Member

16 Posts

Posted - 2013-03-12 : 14:30:12

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..)

ssimon
Starting Member

16 Posts

Posted - 2013-03-13 : 11:37:00

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 11:44:53

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/

ssimon
Starting Member

16 Posts

Posted - 2013-03-13 : 13:16:14

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources