March 12, 2010 at 7:54 am
I was just wondering if there is anyway that you know of to add 20 business days to a date field?
March 12, 2010 at 10:36 am
Best way is to maintain a holiday tablecontaining holidays on out years into the future. (we have a SQL agent job that keeps the table current out 8 years ahead). The holiday table also indicates weekend days (non-business days).
Then you use a tally table outer joined to the holiday tale to generate your business date table that you use to find a proper date... N number of business days from a starting date. This can be done without the use of functions for good performance on vast amounts of data.
The probability of survival is inversely proportional to the angle of arrival.
March 12, 2010 at 11:05 am
We keep a calendar table (100 years, past and future) that has flags for business days, holidays EOM, days of the week, quarters etc.
March 12, 2010 at 12:22 pm
Having a calendar table was my first thought, but how do you populate that table? Do you have to manually enter 8+ years?
Jordon
March 12, 2010 at 12:31 pm
jordon.shaw (3/12/2010)
Having a calendar table was my first thought, but how do you populate that table? Do you have to manually enter 8+ years?Jordon
Jordon here's a simple SQL to use to insert into a permanent Calendar table;
--results
2000-03-15 00:00:00.000
2000-03-16 00:00:00.000
2000-03-17 00:00:00.000
...
--ten years before and after todays date:
with TallyCalendar as (
SELECT dateadd( dd,-3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N
FROM (
SELECT TOP 7300
row_number() OVER (ORDER BY sc1.id) AS RW
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
) X
)
SELECT
--GETDATE() as STARTDATE,
TallyCalendar.N
from TallyCalendar
my example is ten years back and ten years into the future; just change the 3650 (10 years times 365) and 7300 (365 x 20) to a bigger or smaller number.
if you insert into a table, and add some columns like IsHoliday or whatever, you'd have what you are looking for.
Lowell
March 12, 2010 at 12:31 pm
I wrote a stored proc that computes the dates of all known (in our case banking) holidays. Some dates are always fixed (such as July 4th, Nov 11th) others are determined by algorithm (Thanksgiving, Memorial Day, etc.) Other rules for banking holidays need to be applied such as, if a fixed date holiday occurs on Sunday it is observed on Monday, but not if it occurs on Saturday.
All of these holiday definitions are well known and can be generated for any year, past or present. Easter (if you need to consider it as a holiday) is the most complicated algorithm, there rest are straight forward.
The probability of survival is inversely proportional to the angle of arrival.
March 12, 2010 at 12:34 pm
You can generate a lot of it programmatically.
Take a look at the definitions of the holidays. Most of them can be handled in SQL pretty easily.
Some are dead easy, like Christmas, or 4th of July, since they're on a specific date every year.
Thanksgiving is trickier, since it's the fourth Thursday of the month. You can handle that with a pretty straightforward use of the Row_Number() function. Some are last whatever of the month, which can be done with Row_Number() and a descending Order By.
Once you have all of those built, you can usually give your HR department a simple reporting and managing tool for any special holidays or rules. In most companies, HR issues a list of "these are the paid holidays for this year" in either December or January. If they're going to do that, you set them up with a simple application that allows them to confirm and manage the dates, and then to send out the list. That also allows them to manage one-offs and such. If, for example, there's a hurricane and the company has to shut down for a day while everyone evacuates to an alternate site, HR can put that into the calendar. Or if there's a national event that gets added to the list, and so on.
It took me a few hours to generate a decade table the first time I did it, but it's definitely worth the work.
- 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
March 12, 2010 at 12:34 pm
Lowell, thank you for the script.
sturner, would you be willing to share the stored procedure? We're a government agency, so I have a filling that our holidays will be close to the banks.
Thanks,
Jordon
March 12, 2010 at 12:46 pm
sure, shoot me a private message with an email address.
The probability of survival is inversely proportional to the angle of arrival.
March 15, 2010 at 2:11 pm
Ok, I'm close to finishing this up. I now have two new tables, the first one is dbo.calendar, which has a record for everyday for the next 50 years. I then have a second table named dbo.HolidayTable, which has all the holidays and weekends for the next 50 years.
With that being said, if I have another table with a date field named ADDDTTM, how would I query that field, adding 20 days to it; however, 20 days that are not in the HolidayTable, meaning that I will only add business days. Any day, which is Mon-Friday and not a holiday.
Any ideas?
Jordon
March 15, 2010 at 2:35 pm
Okay, here's one way to do it. First, define a CTE for your business dates:
with BizDates (Bdate)
as (
select Date
from Datetable A
left outer join HolidayTable B on A.Date = B.Date
where B.Date i NULL
)
(alternatively you could eliminate the holidays from this table when you generate it)
Now you can get a date that is 10 business days from STARTDATE by:
select Bdate as newDate from
(select Bdate, ROW_NUMBER over (order by Bdate) as bdcnt
from BizDates where Bdate > STARTDATE) BizDayAdd
where bdcnt = 10
there are other ways, (like top(10)) but this is just one.
The probability of survival is inversely proportional to the angle of arrival.
March 15, 2010 at 2:48 pm
So, using this query:
select * from calendar
left join holidaytable
on calendar.[date] = holidaytable.hday
where hday is null
order by date
I'm able to get a list of just my business days.
Now I want to take whenever my adddttm is and add the next 20 rows to my above query. I think that I'm going brain dead and just experiencing a good Monday brain lock!
March 15, 2010 at 2:55 pm
Scott,
I think that I was posting at the same time that you were editing your post. Let me work on what you've posted and see what I can come up with.
Thanks,
Jordon
March 15, 2010 at 2:59 pm
This worked perfectly
with BizDates (Bdate)
as (
select [Date]
from calendar A
left outer join HolidayTable B on A.[Date] = B.Hday
where B.Hday is NULL
)
--(alternatively you could eliminate the holidays from this table when you generate it)
--Now you can get a date that is 10 business days from STARTDATE by:
select Bdate as newDate from
(select Bdate, ROW_NUMBER() over (order by Bdate) as bdcnt
from BizDates where Bdate >= '1/15/2010') BizDayAdd
where bdcnt = 20
Thanks Scott!!!
Jordon
March 15, 2010 at 3:10 pm
yeah, my keyboard glitched before I finished that post so I had to edit it. Glad to have the chance to help out.
You might consider removing the holidays and weekends from your date table at the time you generate it by doing the outer join thing to the holiday table. Then you can select directly from it after that.
Scott
The probability of survival is inversely proportional to the angle of arrival.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply