Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2004-04-05 : 18:53:06
|
| Hi all,I am trying to write a function to calculate all th working days.I am having success formulating just the weekdays and exclude the weekends. I am having difficulty in excluding the holidays.Is there anything similar we have available somewhere?Any help and advice is appreciated.TIA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-05 : 18:56:40
|
| You'll need to have a table that contains your holidays. So you'll link to that table to exclude certain dates.Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-05 : 19:25:23
|
| NOT BETWEEN '01/01/04' AND '12/31/04'That's my favorite way to exclude holidays. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-05 : 19:48:58
|
| As long as I get paid for it!Tara |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-06 : 14:52:11
|
| Back to the original question... one thing to keep in mind is that not everyone (even just within the US) considers the same days to be holidays. For example I worked on an application several years ago that tied into the banking system and we had to track in our calendaring system which days were holidays for the company and which were holidays for the banks. Many times those were not the same. We used a table that would get populated with every day of the year with appropriate flags for BusinessDay and BankingDay. We also had a "default holidays" table to aid in the initial population, but the actual calendar table could be modified with custom schedule changes.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
Glazieyes
Starting Member
1 Post |
|
|
carrey
Starting Member
22 Posts |
Posted - 2004-05-20 : 07:22:33
|
quote:
Originally posted by Glazieyes
Try this link, it explains pretty well how to get working dayshttp://www.revealnet.com/newsletter-v3/0202_G.htm hope it helps sum
This is good if you only need to find out if a particular date falls on a bank holiday or weekend. However, I think that the initial request was to work out the number of business dates between two dates, a very common business problem. Whilst the function describe above could be used iteratively, the perfomance is often very poor.I have had to replace iterative versions of this problem on several occasions as they caused severe performance bottlenecks. Without solving the problem totally for you, here are some strong pointers that will help you develop a robust, flexible solution which can be used for all such needs.1) Build a temp table containing all valid business dates between a range.This can be achieved by using a cartesian product of day, month and year values, applying no. of days per month rules, weekend exclusion and leap year rules in the process. This result set can then be reduced, removing the bank holidays, region specific if required, by deleting with a join to your holiday calendar.e.g.create a table #days containing the numbers 1-31create a table #months containing the numbers 1-12, MonthNames Jan-Dec, days in the month and days in the month in a leap year.create a table #years containing unigue year numbers for the date range being condidered and whether the year is a leap year (search the web for the precide rules for leap years).The cartesian product will create dates using CONVERT(datetime, day + ' + MonthName + ' ' + Year) where the day is <= the numdays appropriate for that month in that year. You'll have to convert the values to char before converting to datetime.2)The number of business days between can then be established for each date or date range by counting the number of records in your buiness date temp table retrned from the proc in 1) that fall between the criteria.The above logic can be quickly and easily built and executes in a fraction of the time that an iterative approach would require.Working this through on your own will be a good lesson on the power of the cartesian product to compute large lists numbers or dates.HTH |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-05-20 : 10:05:25
|
| Thanks for all the suggestions, for now the holidays are just hard codedCREATE FUNCTION IsWorkDay (@dt as DateTime) RETURNS Bit AS BEGIN Declare @dtRoundDown as DateTimeSet @dtRoundDown = Cast(Floor(Cast(@dt as float)) as DateTime)If DatePart(dw, @dtRoundDown) in (1,7) Return(0)If @dtRoundDown in ('somedate1','somedate2',....) Return(0)Return(1)END |
|
|
|
|
|
|
|
|
Function to get the Working Days.