Calculating Work Days
-
Again, lot's of good posts. Shie, Robert L, Noeld, and Adam have all pointed out that the use of a "Calendar Table" or "Holiday Table" to hold non-workdays (I'm assuming neither of these hold M-F normal workdays except maybe when a shift differential must be accounted for, haven't quite figured that one out yet) could be used to augment this function. I took a look at Paul Cresham's UK Bank Holiday script he posted and it certainly looks like you could use it straight up as is or you could modify it to easily populate several years worth of Holiday table.
The flexibility of Holiday tables (Calendar tables) certainly seems to have some merit although, unless you make a pretty big one, will have some limits whereas the functions are much less limited. As with many Date/Time calculations, it sometimes takes more than one function/method to get what you want. In a previous post on this same thread, I suggested that the article should have been called "Calculating Week Days" instead of "Calculating Work Days". Through the intelligent use of a combination of functions like this one and Paul's, Holiday tables, and perhaps, a shift differential function or embedded calculation, you can build the "ultimate work day calculator" for your particular needs.
Again, thank you all for your feedback. I don't know if it's true for you but I've sure learned a lot about what people need or have done concerning the true calculation of work days (not usually weekends but sometimes includes Saturdays, not holidays [we all knew that], not bank holidays, sometimes needs to account for shift differentials) especially for areas outside of the U.S.
Just a reminder... this function wasn't designed to do anything except count the number of week days in a date range much like MS-SQL's DATEEPART calculates, well, date parts. I do appologize for the misnaming of the article.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff:
Personally I would include ALL days in the calendar table -- that way you can easily detect gaps, count days minus holidays in ranges, etc.
The calendar table can't get too big, either -- 365 days / year means that 10 years of coverage is only 3650 rows.
Here's a good article on the subject that also includes a basic script to create one:
http://www.aspfaq.com/show.asp?id=2519
Thanks for starting this thread with your article -- I agree, it was very enlightening!
--
Adam Machanic
whoisactive -
Jeff!
May I contribute my mite?
We may also consider the function
datediff(wk, @startdate, @enddate)
to calculate Sundays between @startdate and @enddate, where end date is included in the time interval but start date is not.
So, if we want to calculate Sundays in the “closed” interval we should use:
datediff(wk, @startdate - 1, @enddate).
If we want to calculate Saturdays, we should turn Saturdays into Sundays by shifting the interval one day forward:
datediff(wk, @startdate, @enddate + 1).
Finally, we can calculate the total number of workdays in the interval by subtracting Sundays and Saturdays from the total number of days:
datediff(dd, @startdate, @enddate + 1)
- datediff(wk, @startdate - 1, @enddate) - datediff(wk, @startdate, @enddate + 1)
Such a variant is language-independent and by my calculations approximately 10% faster (on my computer).
Best regards, Oleg.
-
First, Adam, thanks for the great link. I took a look at ALL of the scripts including the additional reference to the auxiliary number table and I definitely see your point and the case for an auxiliary date table is a powerful one. So powerful that I'll relent and admit that dedicating the necessary disk space is well worth the investment of disk space and the, apparently, very minor maintenance of adding additional years. This is proof that even an old dog can learn new tricks! Thanks for your patience and your posts on this thread. A lot of other people would have lost their cool or just given up.
Just a note, I did some very rough calculations to see what would happen if the auxiliary date table they suggested is setup for 30 years. Each row takes up only 28 bytes (only 1 byte for the two BIT data types, could get 6 more bit columns for “free”). Even if you tripled that size to account for the overhead of VarChar fields, the “Null Bitmaps” of fixed length fields, and the index, etc, that would only be 84 bytes per row or 30660 bytes of storage per year. 30 years would still be under a megabyte which is a pretty darned small table nowadays.
I’ve gotta take back what I said about auxiliary date tables and say they’re very well worth setting up. I’ll eat the crow later, when I've finished cooking it up with extra garlic and a side of humble pie.
Oleg, thanks for the great follow-up. I haven't tested what you suggested but, based on your explanations and looking at the code you posted, it’ll probably work. Considering that I’m probably going to setup my own auxiliary date table, I probably won’t get to testing your suggested alternate code.
Hey Everybody! Thanks again for all of your responses. They were great and I think I learned more than what I thought I was teaching. Like I said, that's one of the reasons this forum is so great!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the great function..but i do have further calrifications...the weekends in uae are Thursdays & fridays. I modified the code to match the weekends but get wrong results..any help on this??
the code is
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = Friday
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = Thursday
THEN 1
ELSE 0
END)
)
when i enter @StartDate = '07/08/2005',@EndDate='07/14/2005'
I get 3..which is wrong..
waiting for urgent help...
-
Here's the calculation you requested... it's quite a bit more complicated that when weekends are Sat/Sun because SQL Server's "Week" datepart always breaks between those two days. And, DateFirst has no effect on that or this would have been very simple.
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk,+4,@EndDate-@StartDate) * 2)
-CASE
WHEN DATENAME(dw,@StartDate) = 'Thursday' THEN 2
WHEN DATENAME(dw,@StartDate) = 'Friday' THEN 1
ELSE 0
END
-CASE
WHEN DATENAME(dw,@EndDate) = 'Thursday' THEN 1
WHEN DATENAME(dw,@EndDate) = 'Friday' THEN 2
ELSE 0
END
+CASE
WHEN DATENAME(dw,@StartDate) IN ('Thursday','Friday')
AND DATENAME(dw,@EndDate) IN ('Thursday','Friday')
THEN 2
ELSE 0
END
You should be aware that this is more appropriately done if you have an "Auxilary Calendar" table (http://www.aspfaq.com/show.asp?id=2519). Then, you code could be written more simply as...
- SELECT COUNT(*)
- FROM dbo.Talley
- WHERE TheDate BETWEEN @StartDate and @EndDate
AND DOW3 NOT IN ('Thu','Fri')
Actually, there's a half-dozen ways to get the same results.
I call my Aux Calendar table "Tally" because I built it so it doubles as an Aux Numbers table (http://www.aspfaq.com/show.asp?id=2516, be careful, the first usage example will not give nulls as you may expect and will not report numbers larger than your Aux Numbers table). I started my table at 01/01/1900 and built it up to 12/31/2099 (73048 rows) because I have to do a lot of retro-date calculations. Most folks start theirs at 01/01/1980 or 01/01/2000 depending on their needs. When properly indexed, even the larger table runs lightning fast. It's worth every byte...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I know everyone is talking about Holidays For this particular function. but guys don't worry to consider holidays in this is very easy. I have modified this SQL little bit and it would take care of holidays also, even though holiday comes on weekend.
First Create Holiday Table
create table HolidayTable (HolDay datetime)
Insert two Holidays in it. (One on weekday and one on weekend just to
make sure function does consider weekend holidays)
insert into HolidayTable values ('01/03/2006')
insert into HolidayTable values ('01/08/2006')
Now change SQL to
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- ( Select count(*) from t2 where HolDay between @StartDate and @EndDate and
(datepart(dw,HolDay) <> 1 and datepart(dw,HolDay)<>7 ))
and you are ALL SET.
Hope this helps,
Best Regards,
Ramesh.
-
For the deep-minded this is a good function, albeit incomplete.
For the rest of us it is simply awesome! Also, congratulations to the author who took the time to analyze the responses and suggest enhancements. This attitude is what makes this forum so excellent.
-
Thanks Carlos! Comments like yours make it all worth while!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What if your country has different public holidays within each individual state or you need to have public holidays for many countries within the same table?
A simple way to implement this into your solution would be to include a bitwise operator within your holidays table. Each bit position could be assigned to different geographical regions. Each entry into the holiday table could then be identified as to which region it is applicable to and whether it should be included in your calculation of work days or not.
Of course this would require the creation of another table identifying the geographical regions and their corresponding bitwise positions but it means that you only need to add the one single integer column to the holidays table.
-- JP
-
Rather than denormalizing with a bit-wise operator, why not just make a simple normalized table of Holidays with the region code?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I guess its a case of multiple dates with one region or one date with multiple regions.
-- JP
-
Sure... but let's just say you're storing the bitmap as 16 bits (using any of a couple of methods)... what do you have to do if you add a 17th region? You have to add another bit. The other way, you just add another row and you don't have to touch code to look at another bit...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yeah I can see the simplicity of that.
Another thought to bring into the equation is the benefits of having a single row for each day of the year within the one single table. Each day can literally only occur once in the real world so only store in one central location each day once. If in future there is a requirement to group or sort certain days of the year in a particular way, simply add the new column and update accordingly.
I have a table I use called DaysOfYear and I use this method to add new criteria as I need it. When I write a script to return days matching specific criteria it means I do not have to join on many tables containing partiular days, it is all contained in the one location.
I am not sure where this fits into best practice, but I personally prefer to work this way. I do accept that one of the flaws of bitwise operators is that you cannot explicitly define the foreign key when creating the table which is not a good thing at all.
-- JP
-
Yep... spot on... couple of us have "Auxilliary Date Tables" that span many years to answer questions like what's the 2 Thursday of July of year xxxx, etc, etc. Between such a table and a decent multi-national holiday table, you can pretty well send any date problem packing. I originally wrote the "work days" function 'cause I couldn't really see the merit in having such a table... pretty much changed my mind since then but still find it to be a nice challenge to hammer out functions like this where DBAs don't allow such tables.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 156 total)
You must be logged in to reply to this topic. Login to reply