October 18, 2007 at 8:58 am
Matt Miller (10/18/2007)
Greg -I think you need to try the solution I posted. The reverse engineering you did has some logic flaws. In particular - you're going to start "losing" business days when you add large numbers of business days.
I broke out the "fixing the start date" if it's not a business day, because that wouldn't be necessary if you can ensure that the start date will NEVER be a non-business day.
Now - there might be a more elegant way to do it, but I'm fairly confident it's accurate.
Sorry Matt, I don't know how I missed that one. That is exactly what I need to do. I never thought about adjusting the start date. I was able to get my code to work as long as the start date was a week day, so I think I will borrow your adjusting the start date part, maybe the whole thing. Thank you so much for your time and effort Matt.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 9:00 am
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
October 18, 2007 at 9:08 am
..still down the 'you want a dates table' path... 😉
Assume we have a very simple one. Fulldate and if the day is a holiday or not.
Haven't tried this on any larger ranges, but since this was just 5 days.. it seems to work ok even when starting on a saturday or sunday....
create table #dates
( fulldate datetime not null, isHoliday bit not null )
insert #dates
select '20071015', 0 union all
select '20071016', 0 union all
select '20071017', 0 union all
select '20071018', 0 union all
select '20071019', 0 union all
select '20071020', 1 union all
select '20071021', 1 union all
select '20071022', 0 union all
select '20071023', 0 union all
select '20071024', 0 union all
select '20071025', 0 union all
select '20071026', 0 union all
select '20071027', 1 union all
select '20071028', 1 union all
select '20071029', 0
declare @today char(8)
set @today = '20071018'
select fulldate
from #dates
where fulldate >= @today
and datediff(day, @today, fulldate) <= 6
and isHoliday = 0
returns the next 5 'workdays' starting with the current day as day one, but skips holidays.
Good enuff? (it's simple anyway) 😉
/Kenneth
October 18, 2007 at 10:09 am
Matt Miller (10/18/2007)
Select @datestart=dateadd(dd,case when cast(cast(@datestart as datetime) as int)%7 >4 then
Matt, this works great! I am curious though, why do you have:
..CAST(CAST(@Datestart AS DATETIME)AS INT)...?
I tried it with CAST(@Datestart AS INT) and it worked the same. I don't understand why it is necessary to cast a datetime variable as datetime. Is there a reason why you have it the way you do?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 10:16 am
I prefer to have it in there when I'm popping in char values like I was for testing (i.e. not count on the implicit conversion). Sounds like you already have valid datetime values to feed it, so it's not needed.
----------------------------------------------------------------------------------
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:31 am
That makes sense. I think there is a problem with it though. If I enter '1/2/2008' as @Datestart which is a Wednesday, and 4 as @DaysToAdd, @DateEnd is 1/7/2007, a Monday, when I should expect the answer to be Tuesday. Assuming I do not want to count @Startdate as one of the days. If I add 5 days, though, the answer, 1/9/2007, is correct. It seems to depend on the number of days I want to add. Or maybe I missed something.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 11:36 am
You're right - I'm not accounting for the "extra days" right. the modulo is misapplied there.
Try this:
declare @dateStart datetime
declare @daysToAdd int
declare @dateEnd datetime
declare @baseday datetime
select cast(cast('1/1/1900' as datetime) as int)%7
select cast(cast('1/2/2008' as datetime) as int)%7
select @datestart='1/3/2008',@daysToAdd=64
--start by making sure your start day IS a business day
Select @datestart=dateadd(dd,case when cast(cast(@datestart as datetime) as int)%7 >4 then
7-cast(cast(@datestart as datetime) as int)%7 else 0 end,@datestart)
select @datestart
--the magic
select @dateEnd=dateadd(dd,
case
when cast(@datestart as int)%7+@daystoadd%5 =5 then 1
when cast(@datestart as int)%7+@daystoadd%5 >5 then 2
else 0 end,
dateadd(dd,@daystoAdd%5,dateadd(wk,@daysToAdd/5,@datestart)))
select @dateend
----------------------------------------------------------------------------------
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 1:13 pm
Matt, I can not tell you how much I appreciate you taking time out of your day to work on this. It seems you are running in to the same problem I was, and that is it will work for certain values of @DaysToAdd, but not others. Your last post works for multiples of 5 only. I tested it up to 1,000,000 days to add and it was still right on. If you try to add only 1 day, however, all bets are off. Same with any other number than a multiple of five. I went ahead and made a function out of it so I could easily see what was happening. If you copy and past the below you will see. In the second column 'NewStart' I should never see a Saturday or Sunday, but if you change the values of @DaysToAdd you will see them. Also, how are you able to post your code like that? Is it an attachment of some kind?
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 @DateStart = DATEADD(dd,CASE WHEN CAST(@DateStart AS INT)%7>4
THEN 7-CAST(@DateStart AS INT)%7 ELSE 0 END,@DateStart)
SELECT @DateEnd = DATEADD(dd,CASE WHEN CAST(@DateStart AS INT)%7+@DaysToAdd%5 =5 THEN 1
WHEN CAST(@DateStart AS INT)%7+@DaysToAdd%5 >5 then 2 ELSE 0 END,
DATEADD(dd,@DaysToAdd%5,DATEADD(wk,@DaysToAdd/5,@DateStart)))
RETURN @DateEnd
END
GO
IF OBJECT_ID('TempDB..#Test','u') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test
(
Start SMALLDATETIME
)
INSERT INTO #Test
SELECT '1/1/2007' UNION ALL
SELECT '1/2/2007' UNION ALL
SELECT '1/3/2007' UNION ALL
SELECT '1/4/2007' UNION ALL
SELECT '1/5/2007' UNION ALL
SELECT '1/6/2007' UNION ALL
SELECT '1/7/2007' UNION ALL
SELECT '1/8/2007' UNION ALL
SELECT '1/9/2007' UNION ALL
SELECT '1/10/2007' UNION ALL
SELECT '1/11/2007' UNION ALL
SELECT '1/12/2007' UNION ALL
SELECT '1/13/2007' UNION ALL
SELECT '1/14/2007' UNION ALL
SELECT '1/15/2007' UNION ALL
SELECT '1/16/2007' UNION ALL
SELECT '1/17/2007' UNION ALL
SELECT '1/18/2007' UNION ALL
SELECT '1/19/2007' UNION ALL
SELECT '1/20/2007' UNION ALL
SELECT '1/21/2007' UNION ALL
SELECT '1/22/2007' UNION ALL
SELECT '1/23/2007' UNION ALL
SELECT '1/24/2007' UNION ALL
SELECT '1/25/2007' UNION ALL
SELECT '1/26/2007' UNION ALL
SELECT '1/27/2007' UNION ALL
SELECT '1/28/2007' UNION ALL
SELECT '1/29/2007' UNION ALL
SELECT '1/30/2007' UNION ALL
SELECT '1/31/2007'
DECLARE @DaysToAdd INT
SELECT @DaysToAdd = 1 --Just change this number
SELECT
Start = DATENAME(dw,start),
NewStart = DATENAME(dw,dbo.fnAddWorkDays(start,@DaysToAdd)),
Start,
NewStart = dbo.fnAddWorkdays(start,@DaysToAdd)
FROM #test
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 2:14 pm
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).
here's round # 3 (FYI - use an IFCode markup of CODE and /CODE to get this highlighted,i.e put those between square brackets):
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/7,dateadd(dd,
case when cast(dateadd(dd,
case when cast(@DateStart as int)%7 >4
then 7-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 7-cast(@DateStart as int)%7
else 0 end,@DateStart)))
RETURN @DateEnd
END
I don't get any saturdays or sundays now. Hopefully third's the charm....
----------------------------------------------------------------------------------
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 2:42 pm
Matt Miller (10/18/2007)
I Kept doing the same thing. I tested one date against Jeff's code, trying to reverse engineer, and miraculously it worked. Now, as you have it there are no saturdays or sundays, but if you try to add one day you will see what is wrong. If @DateStart is Friday, Saturday, or Sunday, @DateEnd should always be the same. In the case of adding one day it should be Monday for all three days, but it is not. I have thought I cracked it so many times now, only to try some obscure number that breaks it.:w00t:
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 2:50 pm
Actually - I was coding for sat-sun to mean start day =monday, not friday. That's where we have THAT difference. I was rolling the start date forward, not backward.
You want THIS:
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/7,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
----------------------------------------------------------------------------------
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 2:57 pm
Matt, I think that did it! I can't test on my real data until I get to work, but on my test table at home it seems to work fine. You're a genious, thank you so much for keeping on it. I think I was almost there, but there is know way I could have done what you did to solve the problem. I keep seeing the modulo used, I guess its time for me to start thinking about it. Thanks again Matt.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 18, 2007 at 5:44 pm
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...
--Jeff Moden
October 18, 2007 at 5:48 pm
Matt... haven't tested it, but I agree... I think you've probably done it and it looks like the performance will outstripe any table solutions in this case... gonna have to put it up against a million rows and see 😉
--Jeff Moden
October 18, 2007 at 7:18 pm
Jeff Moden (10/18/2007)
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...
There is an approach which uses WorkingDays table and does not require RBAR processing.
[Code]
DECLARE @StartDate datetime
SET @StartDate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
-- Table to hold set of dates we are testing
CREATE TABLE dbo.TestTable (StartDate datetime)
-- Insertend 100 days to beused for testing
INSERT INTO dbo.TestTable
SELECT TOP 100 @StartDate - N
FROM dbo.Tally
ORDER BY N
GO
-- I create "Working Dates" table from Tally. I don't bother to exclude weekends, it does not matter
-- I excluded on date interval, it will show if algorithm actually works.
SELECT IDENTITY(int, 1,1) as ID, N_Date As WorkingDate
INTO dbo.TestWorkingDays
FROM dbo.Tally
WHERE N_Date NOT BETWEEN '20071101' and '20071114'
ALTER TABLE dbo.TestWorkingDays
ADD CONSTRAINT PK_TestWorkingDays PRIMARY KEY (ID)
CREATE INDEX IX_WorkingDate ON dbo.TestWorkingDays (WorkingDate)
GO
DECLARE @N int
SET @N = 30 -- Number of working days we need to add
SELECT A.StartDate, T2.WorkingDate as EndDate, DATEDIFF(dd, A.StartDate, T2.WorkingDate) as Duration
FROM TestTable A
INNER JOIN dbo.TestWorkingDays T1 ON T1.WorkingDate = A.StartDate
INNER JOIN dbo.TestWorkingDays T2 ON T2.ID = T1.ID + @N
-- "+ @N" probably is not right. It depend on what do we take as "adding one working day"
-- If "Adding 1 working day" should result in EndDate = StartDate then it must be "+ @N - 1"
ORDER BY A.StartDate
[/Code]
Key feature here is sequential ID numbers in "Working Days" table.
In real life it should not be IDENTITY and it must be re-numbered every time working day added/removed to the table.
But I don't think it's gonna be a significant overhead. How many times Government changed calendar in last 20 years? 😉
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 117 total)
You must be logged in to reply to this topic. Login to reply