January 15, 2009 at 3:44 pm
Hi everyone, I have a cursor to determine when is the next business day but I don't want to keep using it. I have a table that has all my holidays and I ask if the date is not saturday or sunday using:
datepart(weekday, @nfecha) in (7,1)
so if the date which I want to know the next business day is saturday or sunday or is in my holidays table, I add a day to it, until it is not a weekend or is not in my holidays table. Then I get the next record and do the same thing.
Any ideas????? or suggestions??? Any ideas are welcomed.
January 15, 2009 at 4:05 pm
Assuming you have a calendar table with all dates in it:
select
NextBusinessDay = min(a.MyDate)
from
MyDateTable a
where
a.MyDate > @MyStartDateand
a.MyDayOfWeek not in ('Saturday','Sunday')and
a.MyHolidayIndicator <> 1
If you need to load a calendar table, this code on this link will give you a good start:
Date Table Function F_TABLE_DATE
January 16, 2009 at 8:19 am
Saw this thread and just wanted to make a note... recursion is at least as bad as a well formed cursor. Michael's post above is absolutely the way to go. You may or may not want to make a Calendar table that's quite as wide as a lot of people do, but having a Calendar table of some form is definitely the way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2009 at 8:38 am
Calendar table, definitely.
For second best, something like this should do:
;with
Numbers (Number) as
(select top 7 row_number() over (order by object_id)
from sys.all_objects)
Week (Date) as
(select dateadd(day, Number, getdate())
from Numbers)
select min(Date)
from Week
where datepart(weekday, Date) not in (1,7)
and Date not in
(select Holiday
from dbo.MyHolidaysTable);
That will work (assuming you have a table of holidays, which you seem to have). Won't be as efficient as a Calendar table, but will be better than a cursor or recursive solution.
Note that this version suffers from the possibility that the server doesn't treat Sunday as the first day of the week. If that's the case, the Where clause will have to be modified.
Calendar table is best. Has a ton of other uses too. Very handy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply