October 18, 2007 at 7:28 pm
BTW, my Tally table holds 65k dates, it's the whole range of "smalldatetime" dates.
So, "Working Days" table contains 65k - 14 rows.
_____________
Code for TallyGenerator
October 18, 2007 at 8:17 pm
Jeff Moden (10/18/2007)
mrpolecat (10/18/2007)
This works as well though it can only go 1 year out. Not sure about the perfomance with the self join.declare @startdate datetime, @days int
set @startdate = '1/1/2007'
set @days = 20
select count(*) recnum, a.mydate from
(select top 365 n,(dateadd(dd,N,@startdate)) as mydate from tally
where datepart(dw,dateadd(dd,N,@startdate)) not in (1,7) ) a
join
(select top 365 n,(dateadd(dd,N,@startdate)) as mydate from tally
where datepart(dw,dateadd(dd,N,@startdate)) not in (1,7) ) b
on a.n>=b.n
group by a.mydate
having count(*) = @days
order by a.mydate
Heck of a good try... but still a bit of RBAR there... looks almost like a date table but limited to 365 days. No doubt it works great on a single date, but what about a column of dates in a table? Add 20 days to a million random dates and see how fast it is... I think it'll crush performance... but not sure...
I knew it wasn't perfect but by the time I got this far Matt already had it in a headlock so I let it go.
October 18, 2007 at 9:01 pm
How many times Government changed calendar in last 20 years?
Heh... here in the States, ya just don't know... look what they've done to DST, Primary days, and the planet Pluto 😛
I think I like your solution... I'm doing a bit of testing on it now...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 9:16 pm
Ah... sorry, it doesn't work as expected... it includes weekends which is what Greg is trying to exclude... in terms of your example, he wants to add 30 "week days" to the start date...
... but you've given me an idea... thanks...
Now, I gotta try Matt's...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 9:31 pm
Matt Miller (10/18/2007)
Now it just got personal :). Amazing how some good ol' fashion testing seems to work better (I keep trying with not quite enough data to see the flaw, so I apologize).I don't get any saturdays or sundays now. Hopefully third's the charm....
I had high hopes... :hehe:
DECLARE @TestDate DATETIME
SET @TestDate = '20070514'
SELECT @TestDate, dbo.fnAddWorkDays(@TestDate,30) AS [Should be 2007-06-22]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 10:02 pm
Jeff Moden (10/18/2007)
Ah... sorry, it doesn't work as expected... it includes weekends which is what Greg is trying to exclude... in terms of your example, he wants to add 30 "week days" to the start date...... but you've given me an idea... thanks...
Now, I gotta try Matt's...
As I said - it was just a test.
I created one 14 days long "weekend" in my table just for demonstration.
Those 30 days which overlap that "weekend" come out with 44 days difference between StartDate and EndDate.
_____________
Code for TallyGenerator
October 18, 2007 at 10:42 pm
APOLOGIES.....
There was STILL one flaw...was trying to fix some of the f****ing formatting, and I blew out a 5 and replaced it with a 7. Had been right in previous versions.
Jeff - this one assumes you don't count today in the number to add, so - monday + 5 = next monday. It also assumes that if your start date is on a weekend, you "fall back" to the PREVIOUS workday (i.e. friday), and start the count from there.
anyway...
use test
go
IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWorkdays
GO
CREATE FUNCTION dbo.fnAddWorkdays (@DateStart DATETIME,@DaysToAdd INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @DateEnd DATETIME
SELECT @DateEnd = dateadd(wk,@daystoAdd/5,
dateadd(dd, case when cast(dateadd(dd,
case when cast(@DateStart as int)%7 >4
then 4-cast(@DateStart as int)%7
else 0 end,
@DateStart) as int)%7+@daysToAdd%5>4
then 2
else 0 end+@daysToAdd%5,
dateadd(dd,
case when cast(@DateStart as int)%7 >4
then 4-cast(@DateStart as int)%7
else 0 end,@DateStart)))
RETURN @DateEnd
END
GO
DECLARE @TestDate DATETIME
SET @TestDate = '20070514'
SELECT @TestDate, dbo.fnAddWorkDays(@TestDate,30) AS [Should be 2007-06-22] --no, 6/25
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 18, 2007 at 10:47 pm
Heh... now you've made a real mess of the formatting... :hehe:
Ooops... never mind... dunno what happened but it went back to a good format when I posted this message...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 11:31 pm
First, nicely done, Matt! Yeah, if you don't include the start date as a Week Day, then you're correct... adding 30 Weekdays to '20070514' would, in fact, be 6/25 instead of 6/22. I also tested it using 7 days starting on '20070512' through '20070521' as starting days and it worked flawlessly. Nice job. I'll test it for performance soon.
In the mean time... It took me a couple of hours to figure it out, but I took quite a different approach... and, the function I built gives you the choice of whether to include or exclude the starting day... of course, I did it for U.S. applications... some mods would have to be made for non-U.S. apps...
First, gotta have some test data... lots of it... just in case someone doesn't have it by now, here's my infamous million row test table, again...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Takes about 77 seconds to execute.
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),
SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
+ CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),
SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)
INTO dbo.JBMTest
FROM dbo.Tally t1,
dbo.Tally t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
And here's the code I wrote to add 30 days to every date... in a million row table... in about 21 seconds... (GRID MODE FOLKS!) 😀
DECLARE @DaysToAdd INT
SET @DaysToAdd = 30
SELECT d.SomeDate,d.EndDate
+ CASE WHEN DATENAME(dw,d.EndDate) = 'Saturday' THEN 2
WHEN DATENAME(dw,d.EndDate) = 'Sunday' THEN 1
ELSE 0
END AS EndDate
FROM
(
SELECT SomeDate,
DATEADD(wk,@DaysToAdd/5, SomeDate
+ CASE WHEN DATENAME(dw,SomeDate) = 'Saturday' THEN 2
WHEN DATENAME(dw,SomeDate) = 'Sunday' THEN 1
ELSE 0
END
+ @DaysToAdd%5
) AS EndDate
FROM jbmTest
)d
Of course, to make it a bit more generic for any table, you can turn it into a function...
CREATE FUNCTION dbo.AddWorkDays(@StartDate DATETIME, @DaysToAdd INT, @CountToday INT)
RETURNS DATETIME
AS
BEGIN --------------------------------------------------------------------------------------
RETURN (
SELECT d.EndDate
+ CASE WHEN DATENAME(dw,d.EndDate) = 'Saturday' THEN 2
WHEN DATENAME(dw,d.EndDate) = 'Sunday' THEN 1
ELSE 0
END AS EndDate
FROM (SELECT DATEADD(wk,@DaysToAdd/5, @StartDate
+ CASE WHEN DATENAME(dw,@StartDate) = 'Saturday' THEN 2
WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1
ELSE 0
END
+ @DaysToAdd%5-@CountToday
) AS EndDate
)d
) --End of Return
END --End of Function
GO
SELECT SomeDate, dbo.AddWorkDays(SomeDate, 30, 0)
FROM dbo.jbmTest
As expected, the use of a UDF slowed it down quite a bit... takes 35 seconds instead of 21 like in the straight batch code... Still, let's see someone do THAT to a million rows in that short a time using a date table 😛
Since I used comparisons against string literals, it may be that it'll turn out that Matt's code is faster than mine... dunno yet... wait one...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 11:34 pm
Ok... just ran Matt's code against the million row table... Ladies and Gentlemen, we have a tie! :w00t: Matt's function took the same amount of time as mine.
One thing that everyone should note... we haven't tested either function for a negative number of days... but I'm pooped and I gotta get my beauty sleep 😛
See ya tomorrow...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2007 at 12:11 am
Following user defined function returns number of weekdays between two dates specified. This function excludes the dates which are passed as input params. It excludes Saturday and Sunday as they are weekends. I always had this function with for reference but after some research I found original source website of the function. This function has been written by Author Alexander Chigrik.
CREATE FUNCTION dbo.spDBA_GetWeekDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(day, @StartDate, @EndDate) 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
RETURN ( @WorkDays )
END
GOThis function can be used as
SELECT dbo.spDBA_GetWeekDays ('10/10/2005', '11/22/2005')
GO
October 19, 2007 at 2:12 am
And, here's my "weekdays between dates" original post... just for reference 😀
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/
Part of what's so good about it is that it doesn't need to use a function... makes it very high speed.
But, that's not what we're talking about on this thread... we're talking about adding weekdays to a given date.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2007 at 2:43 am
Funny story guys is that your UDF's don't work.
No matter how hard you try to perfect it.
They don't work by definition.
And nobody can do anything about it.
At least they don't work for me.
Because when I need to add working days I need to add working days. Public Holidays not to be included.
And because we work for Australian an New Zealand customers.
And there is such thing as Queens Birthday on this side of the world.
And can you imagine? They are different in these 2 countries (neither of those "birthdays" is the actual birthday of the Queen, but it's another story :))
So, no matter which function I use - it will return wrong results for some of our customers.
Not to mention problem with the Easter. 😉
So, for me table WorkDays seems inevitable for this task.
Would be nice to see any other solutions.
_____________
Code for TallyGenerator
October 19, 2007 at 5:03 am
Sergiy (10/19/2007)
Funny story guys is that your UDF's don't work.No matter how hard you try to perfect it.
They don't work by definition.
And nobody can do anything about it.
At least they don't work for me.
Because when I need to add working days I need to add working days. Public Holidays not to be included.
And because we work for Australian an New Zealand customers.
And there is such thing as Queens Birthday on this side of the world.
And can you imagine? They are different in these 2 countries (neither of those "birthdays" is the actual birthday of the Queen, but it's another story :))
So, no matter which function I use - it will return wrong results for some of our customers.
Not to mention problem with the Easter. 😉
So, for me table WorkDays seems inevitable for this task.
Would be nice to see any other solutions.
Sergiy, wouldn't it be possible to have a small table of only hollidays, and if @DateEnd falls on one of those days just add 1?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 19, 2007 at 5:28 am
Matt Miller (10/18/2007)
APOLOGIES.....There was STILL one flaw...was trying to fix some of the f****ing formatting, and I blew out a 5 and replaced it with a 7. Had been right in previous versions.
Jeff - this one assumes you don't count today in the number to add, so - monday + 5 = next monday. It also assumes that if your start date is on a weekend, you "fall back" to the PREVIOUS workday (i.e. friday), and start the count from there.
anyway...
use test
go
IF OBJECT_ID('dbo.fnAddWorkdays','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWorkdays
GO
CREATE FUNCTION dbo.fnAddWorkdays (@DateStart DATETIME,@DaysToAdd INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @DateEnd DATETIME
SELECT @DateEnd = dateadd(wk,@daystoAdd/5,
dateadd(dd, case when cast(dateadd(dd,
case when cast(@DateStart as int)%7 >4
then 4-cast(@DateStart as int)%7
else 0 end,
@DateStart) as int)%7+@daysToAdd%5>4
then 2
else 0 end+@daysToAdd%5,
dateadd(dd,
case when cast(@DateStart as int)%7 >4
then 4-cast(@DateStart as int)%7
else 0 end,@DateStart)))
RETURN @DateEnd
END
GO
DECLARE @TestDate DATETIME
SET @TestDate = '20070514'
SELECT @TestDate, dbo.fnAddWorkDays(@TestDate,30) AS [Should be 2007-06-22] --no, 6/25
Matt, this works like a charm. I can't thank you enough, now it is going to take me days just to figure out what you did. Jeff, I have not had a chance to try yours yet, I am going to, though can't be today.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 31 through 45 (of 117 total)
You must be logged in to reply to this topic. Login to reply