| Author |
 Topic  |
|
|
ssimon
Starting Member
USA
12 Posts |
 Posted - 03/12/2013 : 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)) |
Edited by - ssimon on 03/13/2013 13:16:35
|
|
|
James K
Flowing Fount of Yak Knowledge
1518 Posts |
Posted - 03/12/2013 : 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
India
47173 Posts |
Posted - 03/12/2013 : 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
USA
12 Posts |
Posted - 03/12/2013 : 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
USA
12 Posts |
Posted - 03/13/2013 : 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
India
47173 Posts |
Posted - 03/13/2013 : 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
USA
12 Posts |
Posted - 03/13/2013 : 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! |
|
|
| |
Topic |
|
|
|
SOLVED - DateAdd subtracting Workdays
