Stored Procedure to generate work day prior?

Question

Post reply

Stored Procedure to generate work day prior?

bkmooney

SSCrazy

Points: 2041
More actions
December 9, 2008 at 3:04 pm

#198559

I am developing a database that has certain tables organized by date/time, however these tables do not contain any entries for days that are not workdays (ie are weekends or holidays).
To avoid NULLS associated with missing days where there is no work, I'd like to create a stored procedure that I can give a date, and then have it return the date of the prior workday.
In math/set notation, this would be something like:
given a datetime object, date:
return: Max(d < date | ( ( datename(wd, d) not in {Saturday, Sunday} ) AND (d not in Holiday) )
where Holiday is a finite set of known holidays.
I'm still really new to SQL, so I'm having trouble getting my head around how I could prossibly write this, or if even a stored procedure is the right way to go about this.
Any ideas or thoughts are much appreciated.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

May I suggest your reading the following. The first is by Jeff Moden and is EXCELLENT

http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

http://www.sqlservercentral.com/scripts/DateDiff/63346/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

bkmooney SSCrazy Points: 2041 More actions · Answer 2

You were right - Jeff's article was very helpful for handling weekends in the function I'm writing, but I still don't have a very efficient way to account for all the holidays.

Below is the function I have created that returns the prior Market Day based on the NYSE calendar for 2005-2010.

If anyone has a more efficient way of doing this, I would definitely appreciate being pointed in the right direction.

I've found the best date range to test this function is 12-20-2006 through 1-5-2007. There's a 3 day weekend (incl X-mas) and a 4 day weekend (New Yrs + memory of Gerald Ford) in there that the function needs to avoid.

Thanks!

----------------------------------------------------------

ALTER FUNCTION dbo.get_MarketDayPrior(@date SMALLDATETIME)

RETURNS SMALLDATETIME

AS

BEGIN

DECLARE

@AdjStart DATETIME,

@IntDays INT

SELECT @AdjStart=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @date)))

-- Calculates

SELECT @IntDays=1

SELECT @IntDays=@IntDays+

(CASE WHEN DATEPART(dw, @AdjStart-@IntDays)%6=1 THEN 1 ELSE 0 END

-- 2005 Holidays---------------

+ CASE WHEN (@AdjStart-@IntDays)='2005-01-01' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-01-17' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-02-21' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-03-25' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-05-30' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-07-04' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-09-05' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-11-24' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2005-12-25' THEN 1 ELSE 0 END

-- 2006 Holidays---------------

+ CASE WHEN (@AdjStart-@IntDays)='2006-01-01' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-01-16' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-02-20' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-04-14' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-05-29' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-07-04' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-09-04' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-11-23' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2006-12-25' THEN 1 ELSE 0 END

-- 2007 Holidays---------------

+ CASE WHEN (@AdjStart-@IntDays)='2007-01-01' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-01-02' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-01-15' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-02-19' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-04-06' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-05-28' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-07-04' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-09-03' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-11-22' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2007-12-25' THEN 1 ELSE 0 END

-- 2008 Holidays---------------

+ CASE WHEN (@AdjStart-@IntDays)='2008-01-01' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-01-21' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-02-18' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-03-21' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-05-26' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-07-04' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-09-01' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-11-27' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2008-12-25' THEN 1 ELSE 0 END

-- 2009 Holidays---------------

+ CASE WHEN (@AdjStart-@IntDays)='2009-01-01' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-01-19' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-02-16' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-04-10' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-05-25' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-07-03' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-09-07' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-11-26' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2009-12-25' THEN 1 ELSE 0 END

-- 2010 Holidays---------------

+ CASE WHEN (@AdjStart-@IntDays)='2010-01-01' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-01-18' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-02-15' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-04-02' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-05-31' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-07-05' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-09-06' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-11-24' THEN 1 ELSE 0 END

+ CASE WHEN (@AdjStart-@IntDays)='2010-12-24' THEN 1 ELSE 0 END

)