October 24, 2007 at 4:42 am
When I was sick I was better.
:hehe:
Return to the original version:
[Code]
IF OBJECT_ID('dbo.fnAddWeekdays_Sergiy','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWeekdays_Sergiy
GO
CREATE FUNCTION dbo.fnAddWeekdays_Sergiy (@Start DATETIME, @Days INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @End DATETIME
SELECT @End = @Start + @Days/5*7 + @Days%5 +
CASE WHEN (@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5 > 5
THEN 2
ELSE 0 END
RETURN @End
END
[/Code]
Tested with this:
[Code]
declare @D datetime
SET @D = '20071017'
SELECT @D+T1.Number-1 StartDate, T2.Number Days,
[dbo].[fnAddWeekdays_Sergiy](@D+T1.Number-1, T2.Number) EndDay,
datename(dw, @D+T1.Number-1),
datename(dw, [dbo].[fnAddWeekdays_Sergiy](@D+T1.Number-1, T2.Number))
FROM Tally T1, Tally T2
WHERE T1.Number < 8 AND T2.Number < 15
AND (@@DATEFIRST + (DATEPART(dw, @D+T1.Number-1) - 2)) % 7 < 5
ORDER BY T1.Number, T2.Number
[/Code]
Seems all right.
_____________
Code for TallyGenerator
October 24, 2007 at 4:51 am
what u r trying to convince with these statements?
October 24, 2007 at 5:23 am
Hmm...
Actually it was simply an answer on the previous post.
The one from Michael Meierruth.
:ermm:
_____________
Code for TallyGenerator
October 24, 2007 at 5:59 am
Some of you know me by now.
I just couldn't resist this one!
Try this inline-function for speed, please...
CREATE FUNCTION [dbo].[fnAddWorkdays]
(
@StartDate DATETIME,
@DaysToAdd INT
)
RETURNS DATETIME
AS
BEGIN
RETURN CREATE FUNCTION [dbo].[fnAddWorkdays]
(
@StartDate DATETIME,
@DaysToAdd INT
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(DAY, CASE DATEDIFF(DAY, '17530101', @StartDate) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END + @DaysToAdd / 5 * 7 + @DaysToAdd % 5 + CASE DATEDIFF(DAY, '17530101', @StartDate + @DaysToAdd / 5 * 7 + @DaysToAdd % 5 + CASE DATEDIFF(DAY, '17530101', @StartDate) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END, @StartDate)
END
END
N 56°04'39.16"
E 12°55'05.25"
October 24, 2007 at 6:09 am
Sergiy (10/24/2007)
Hmm...Actually it was simply an answer on the previous post.
The one from Michael Meierruth.
:ermm:
Sergiy,
When the start date is a workday it works fine. When the start date is a Sunday it fails, e.g.
select dbo.fnAddWeekdays_Sergiy('20071020',3) -- returns 20071025
select dbo.fnAddWeekdays_Sergiy('20071021',3) -- returns 20071026
select dbo.fnAddWeekdays_Sergiy('20071022',3) -- returns 20071025
It fails if the number of days is negative. But then again you might not be interested in that functionality.
So I guess your function is meant not to include the start date.
Jeff,
I just realized that your Workdays routine actually works just fine when the start date is greater than the end date. I must have dropped a bit somewhere in a copy/paste operation. Sorry if I mislead.
October 24, 2007 at 10:48 am
Peter Larsson (10/24/2007)
Some of you know me by now.I just couldn't resist this one!
Wow, really close. It works with positive values for all starting dates. It only fails when subtracting dates and the starting date falls on a weekend. For example, subtracting one day from '2007-10-20' results in '2007-10-22' -- it actually adds two days :(. If this can be fixed, great. If not, you'll have to check for negative values and reject them.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 24, 2007 at 11:10 am
For what it's worth Tomm - Greg's requirements seemed to be a little different (at least the ones at first), which is why several of the functions "fail" your test.
Main differences (as requested by OP Greg):
- function should NEVER return a weekend date (so adding 0 puts you on your default day, which meant it WOULD move the date).
- don't count the start date
- if start date is on a weekend, then roll the date BACK (meaning the prior friday, not the following monday).
Then the req's got a little muddy after that (with making the "count the start date", rolling forward vs rolling backwards)
Kind of hard to compare the functions prior to your submission to yours when the requirements change.
----------------------------------------------------------------------------------
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 24, 2007 at 11:18 am
Matt Miller (10/24/2007)
Then the req's got a little muddy after that (with making the "count the start date", rolling forward vs rolling backwards)Kind of hard to compare the functions prior to your submission to yours when the requirements change.
Matt, I didn't mean to change requirements. Still the same, but I did say I liked Jeff's function because I had the option of counting start day if every I needed to. Both yours and Jeffs work precisely how I need them to. I will now try Sergiy's, since he recognized there was a typo.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2007 at 11:24 am
Greg Snidow (10/24/2007)
Matt, I didn't mean to change requirements. Still the same, but I did say I liked Jeff's function because I had the option of counting start day if every I needed to. Both yours and Jeffs work precisely how I need them to. I will now try Sergiy's, since he recognized there was a typo.
Oh I understand, and I wasn't assigning blame - just pointing out the design differences. "fluid" requirements kind of are par for the course on here 😛
----------------------------------------------------------------------------------
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 24, 2007 at 12:24 pm
Matt Miller (10/24/2007)
For what it's worth Tomm - Greg's requirements seemed to be a little different (at least the ones at first), which is why several of the functions "fail" your test.
Yes, I realize that. However, as seems to happen quite a lot here, someone has taken the original requirements and run with them. In this case, it would seem to be me.:blush:
We don't do this where I work (add work days to a date) but we do have the function that returns the number of work days between two dates. However, adding workdays would be handy to have for projections and other types of analysis so I have put a little preparatory effort into this. My goal is to come up with generally acceptable definitions and rules that all of us could accept and work with, not just the OP.
Here is what I've come up with.
1 - in this context, "work days" are Monday through Friday, inclusive.
2 - the duration of a work day is immaterial. One work day shall be considered to be from the beginning of one work day to the beginning of the next work day (or the beginning of the previous work day).
3 - When adding business days to a reference date, both the reference date and the resulting date shall be a work day.
4 - Because reference dates may originate from varied sources and cannot be depended upon to always be a work day, any reference date that happens to be on a weekend (Saturday or Sunday) shall be treated as if it were the first work day of the week (Monday). When adding work days results in a date that happens to be on a weekend, the final result is also made to be the following Monday.
I had a couple more but I chucked'em because they were too tangential. These rules are open to discussion and criticism (as if I could prevent it! :)) Actually, you will notice that I have gleaned these from previous discussions so they have already been pretty thoroughly discussed.
However, in looking ever these definitions, I realized that the rules I had previously posted did not fit. Here are the revised rules.
* Adding one work day to a Saturday, Sunday or Monday should land you on the next Tuesday.
* Adding one work day to a Friday should land you on the following Monday.
* Adding one work day to any other day should land you on the next day.
* Subtracting one work day from a Saturday, Sunday or Monday should land you on the previous Friday.
* Subtracting one work day from any other day should land you on the previous day.
* Adding zero work days to a date will result in the original date unless the original date is a Sunday or Monday in which case the result will be the next Monday.
And the test cases have also changed to reflect the new rules.
select dbo.BizDaysAdd ('2007-10-20', 0) -- should be '2007-10-22'
select dbo.BizDaysAdd ('2007-10-20', 260) -- should be '2007-10-20'
select dbo.BizDaysAdd ('2007-10-20', 1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-21', 1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-22', 1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-23', 1) -- should be '2007-10-24'
select dbo.BizDaysAdd ('2007-10-24', 1) -- should be '2007-10-25'
select dbo.BizDaysAdd ('2007-10-25', 1) -- should be '2007-10-26'
select dbo.BizDaysAdd ('2007-10-26', 1) -- should be '2007-10-29'
select dbo.BizDaysAdd ('2007-10-20', 2) -- should be '2007-10-24'
select dbo.BizDaysAdd ('2007-10-20', 3) -- should be '2007-10-25'
select dbo.BizDaysAdd ('2007-10-20', 4) -- should be '2007-10-26'
select dbo.BizDaysAdd ('2007-10-20', 5) -- should be '2007-10-29'
select dbo.BizDaysAdd ('2007-10-20', 6) -- should be '2007-10-30'
select dbo.BizDaysAdd ('2007-10-20', 7) -- should be '2007-10-31'
select dbo.BizDaysAdd ('2007-10-20', 8) -- should be '2007-11-01'
select dbo.BizDaysAdd ('2007-10-20', 9) -- should be '2007-11-02'
select dbo.BizDaysAdd ('2007-10-20', 10) -- should be '2007-11-05'
select dbo.BizDaysAdd ('2007-10-20', -1) -- should be '2007-10-19'
select dbo.BizDaysAdd ('2007-10-21', -1) -- should be '2007-10-19'
select dbo.BizDaysAdd ('2007-10-22', -1) -- should be '2007-10-19'
select dbo.BizDaysAdd ('2007-10-23', -1) -- should be '2007-10-22'
select dbo.BizDaysAdd ('2007-10-24', -1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-25', -1) -- should be '2007-10-24'
select dbo.BizDaysAdd ('2007-10-26', -1) -- should be '2007-10-25'
select dbo.BizDaysAdd ('2007-10-20', -2) -- should be '2007-10-18'
select dbo.BizDaysAdd ('2007-10-20', -3) -- should be '2007-10-17'
select dbo.BizDaysAdd ('2007-10-20', -4) -- should be '2007-10-16'
select dbo.BizDaysAdd ('2007-10-20', -5) -- should be '2007-10-15'
select dbo.BizDaysAdd ('2007-10-20', -6) -- should be '2007-10-12'
select dbo.BizDaysAdd ('2007-10-20', -7) -- should be '2007-10-11'
select dbo.BizDaysAdd ('2007-10-20', -8) -- should be '2007-10-10'
select dbo.BizDaysAdd ('2007-10-20', -9) -- should be '2007-10-09'
select dbo.BizDaysAdd ('2007-10-20', -10) -- should be '2007-10-08'
Unfortunately, I haven't had the time to change my function to work with these new rules (this is "spare time" effort and I'm a little short at the moment) or to test the other submissions. So... more later.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 24, 2007 at 1:48 pm
Michael Meierruth (10/24/2007)
Sergiy,
When the start date is a workday it works fine. When the start date is a Sunday it fails
How do you know it fails?
If you read several pages ago what I made this function for you'll see how it supposes to be used.
I have no idea what's the business rule for records with StartDate outside of business days, Greg never posted anything about that.
And yours or mine assumptions don't matter in this case.
Function can be easily changed if the rule will be applied.
By just modifying @StartDate at the beginning.
_____________
Code for TallyGenerator
October 24, 2007 at 1:51 pm
Tomm Carr (10/24/2007)
Here is what I've come up with.1 - in this context, "work days" are Monday through Friday, inclusive.
2 - the duration of a work day is immaterial. One work day shall be considered to be from the beginning of one work day to the beginning of the next work day (or the beginning of the previous work day).
3 - When adding business days to a reference date, both the reference date and the resulting date shall be a work day.
4 - Because reference dates may originate from varied sources and cannot be depended upon to always be a work day, any reference date that happens to be on a weekend (Saturday or Sunday) shall be treated as if it were the first work day of the week (Monday). When adding work days results in a date that happens to be on a weekend, the final result is also made to be the following Monday.
Tomm, that seems to fit my original needs. As for number 3 though, since I put the function into play(I won't say whos), one person came to me to say emails were coming a day late, and another person said they were coming right on time. That is what made me think about Jeff's extra parameter. I don't know if you are going to get everyone in an office to agree.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2007 at 1:58 pm
Greg Snidow (10/24/2007)
... one person came to me to say emails were coming a day late, and another person said they were coming right on time. That is what made me think about Jeff's extra parameter. I don't know if you are going to get everyone in an office to agree.
That's exactly the reason why I don't start anything without written requirements.
And if I see some diversions I point on them and FORCE customer to sign one of them.
Interviewing customers is about 50% of development.
😉
_____________
Code for TallyGenerator
October 24, 2007 at 2:14 pm
Sergiy (10/24/2007)
Greg Snidow (10/24/2007)
... one person came to me to say emails were coming a day late, and another person said they were coming right on time. That is what made me think about Jeff's extra parameter. I don't know if you are going to get everyone in an office to agree.That's exactly the reason why I don't start anything without written requirements.
And if I see some diversions I point on them and FORCE customer to sign one of them.
Interviewing customers is about 50% of development.
😉
For me that is a minor issue, as in my office I think I can get folks to agree on whether to count the first day or not. What I can not change is the fact that sometime someone may be working on a Saturday, so I need to deal with those. Your function works as long as the start date is not a saturday or sunday, but heck, i've learned so much from you fine folks on this thread that I think I will be able to fix it myself. Thanks for all the help.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 24, 2007 at 4:55 pm
Greg Snidow (10/24/2007)
For me that is a minor issue, as in my office I think I can get folks to agree on whether to count the first day or not. What I can not change is the fact that sometime someone may be working on a Saturday, so I need to deal with those.
What I have found particularly helpful, both in obtaining user requirements and in code/design reviews: first nail down the general case and only then take on the exceptions and deviations. If subsequent effort shows a flaw in the general case, drop everything else until you get that sucker beat back into shape. If someone objects whining about all the effort made so far that will have to be done over again, have something handy with which you can inflict much pain to show him the error of his ways.:laugh:
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 15 posts - 91 through 105 (of 117 total)
You must be logged in to reply to this topic. Login to reply