x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Weekends, Holidays and other reasons to Party

OK, so I really never need a reason.  The question ofter arises.

“How can I count the number of days between 2 dates, but exclude weekends and holidays”

EDIT: In this thread, Dr. Cross Join(aka Jeff Smith) posts a method where you don't need to add weekends.  I gotta test it out yet, but I pretty sure Jeff's right.

Here's How

CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))
GO

SET NOCOUNT ON
DECLARE @FirstSat datetime, @x int
SELECT @FirstSat = '1/3/2004', @x = 1

--Add WeekEnds
WHILE @x < 52
BEGIN
 INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
 SELECT DATEADD(ww,@x,@FirstSat),   'SAT' UNION ALL
 SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN'
 SELECT
@x = @x + 1
END
SET NOCOUNT OFF
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO

-- Add US Holidaze
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT '1/1/2004',   'THU' UNION ALL
SELECT '2/16/2004',  'MON' UNION ALL
SELECT '5/31/2004',  'MON' UNION ALL
SELECT '7/5/2004',   'MON' UNION ALL
SELECT '9/6/2004',   'MON' UNION ALL
SELECT '11/25/2004', 'THU' UNION ALL
SELECT '11/26/2004', 'FRI' UNION ALL
SELECT '12/24/2004', 'FRI' UNION ALL
SELECT '12/31/2004', 'FRI'
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO


-- WeekDays in May

DECLARE @Start datetime, @End datetime
SELECT @Start = '5/1/2004', @End = '6/1/2004'

SELECT  DATEDIFF(dd,@Start,@End)- COUNT(*) AS WeekDays
  FROM WeekEndsAndHolidays
 WHERE DayOfWeekDate BETWEEN @Start AND @End
GO

 

Print | posted on Thursday, May 12, 2005 2:36 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Weekends, Holidays and other reasons to Party

so what happens when the next year rolls around? you're going to have to go back and change this all up. storing the holidays in a table i can see, but weekends can be figured out using the DATEPART function and pass in "dw" to get the weekday.

from books online:
"The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week."

this comes up a lot in business when tracking how long it takes to ship things, or how long to build things. since a lot of places don't work on holidays or weekends, it skews the results.

i could come back and say it took 5 days to process something, but in actually i only worked 3 of those 5 days, so it only took 3 days.
5/12/2005 3:05 PM | ron
Gravatar

# re: Weekends, Holidays and other reasons to Party

here's a function to calculate the number of weekdays between two dates. it doesn't take into account holidays though:

i'm sure it can be optimized some more, or written better, but it works. we use it all the time.


CREATE FUNCTION calculate_weekday_function
(
@d1 datetime,
@d2 datetime
)
RETURNS int AS
BEGIN
DECLARE @d1s datetime
DECLARE @d2s datetime

DECLARE @days int
DECLARE @wks int

--get first sunday before first date
IF (DATEPART(dw, @d1) > 1)
SET @d1s = DATEADD(dd, 1 - DATEPART(dw, @d1), @d1)
ELSE
SET @d1s = @d1

--get next sunday after last date
IF (DATEPART(dw, @d2) > 1)
SET @d2s = DATEADD(dd, 8 - DATEPART(dw, @d2), @d2)
ELSE
SET @d2s = @d2

SET @wks = DATEDIFF(wk, @d1s, @d2s)
SET @days = (@wks * 7) - (@wks * 2) - DATEDIFF(dd, @d2, DATEADD(dd, -2, @d2s)) - DATEDIFF(dd, @d1s, @d1)

RETURN ABS(@days)
END

5/12/2005 3:07 PM | ron
Gravatar

# re: Weekends, Holidays and other reasons to Party

If you take a look at the thread I mentioned...Jeff came up with a very simple solution to that problem that fit directly in with what I blogged here.

Have a look, and thanks for the post.

5/12/2005 3:16 PM | Brett (Not just a Number...huh?)
Gravatar

# re: Weekends, Holidays and other reasons to Party

if you're going to have a calendar table, then you really should have saturdays and sundays in there as well, as the table name "WeekEndsAndHolidays" suggests, and dispense with the DATEDIFF calculation, which is superfluous

note: another column in the calendar table should be "hours worked" so that you can track early closings like christmas eve

check this answer i wrote 4 years ago --
http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid396887_tax301455,00.html (site registration may be required, but it's free -- or just turn off javascript before visiting)

yes, i know some guys are going to complain "but doesn't this mean i have to generate a new calendar table every year?" and the answer is yes, you do, just take a few minutes to get it right, would you? it will make the queries you run all year really simple

;o)
5/15/2005 5:28 PM | rudy
Gravatar

# re: Weekends, Holidays and other reasons to Party

Great input Rudy...Thanks....hopefully this blog, and your link, will put an end to this question.

Are there any questions that have not been asked?

5/16/2005 9:11 AM | Brett (Not just a Number...huh?)
Gravatar

# re: Weekends, Holidays and other reasons to Party

uh .... Rudy -- his table does have weekends in there...
5/17/2005 8:25 PM | Jeff S
Gravatar

# re: Weekends, Holidays and other reasons to Party

Maybe I should have labeld the output as WorkDays...Not WeekDays...since the hoilday is usualy on the weekday. There are exceptions of course.

Plus to point out that it might be good to have a country code and or state/province/whatever...I think Northwind uses Region code to define holidays to the local country.

Bank Holidays...ya gotta love the Irish....

5/18/2005 9:00 AM | Brett (Not just a Number...huh?)
Gravatar

# re: Weekends, Holidays and other reasons to Party

As another aside as well...

It might be good to have a [Name] Column that contains the name of a special day, other than just the Day of the week..

To Rudy's point about having to build one out every year.

I'd probably go out 30 years. Wouldn't be hard.

And at that time if it needs to be updated...I won't be around to care I would imagine. I guess you would have to worry about adding or removing holidays for a particular year...but it's been pretty stable the last severl years here though.

Come to think of it, you might have to even add a column of business sector, or gov vs. non government.

For example I know that even withing my Comapny that the financial sector had Good Friday off because the street was closed. We were open. Thatnk goodness I have a lot of days accrued.

5/18/2005 9:05 AM | Brett (Not just a Number...huh?)
Gravatar

# Fun with Dates (Date Conversion examples)

6/2/2005 12:08 PM | x002548's Blog
Gravatar

# Fun with Dates (Date Conversion examples)

6/2/2005 12:51 PM | x002548's Blog
Gravatar

# re: Weekends, Holidays and other reasons to Party

GOOD
Example
its usefull for the people
whi is working on industrial solutions
10/8/2005 3:47 AM | Madan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET