Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Finding the Next Business Day Recursively Expand / Collapse
Author
Message
Posted Thursday, February 7, 2008 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 8:00 AM
Points: 6, Visits: 25
I was looking for a quick way to find the 5th business day of each month and this function handles it pretty well, except I have to find it from the end of last month, it seems, rather than the first of this month.

I used the following and it works pretty consistently so far, but I'm still testing.

declare @EndOfLastMonth datetime
select @EndOfLastMonth = dateadd(dd,-day(getdate()),convert(varchar(10),getdate(),120))
select dbo.fnGetNextBusinessDay (@EndOfLastMonth, 5)


Thanks Rob! This made a several-hours task into a several-minutes one.



Post #452787
Posted Thursday, February 7, 2008 9:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:15 PM
Points: 1,945, Visits: 3,008
This simply does not work. There is no way to compute all the holiday in the Common Era Calendar. Where is your calculation for Easter? Chinese New Year? etc.

You have to use an auxiliary Calendar table with the business days in it. Google around for details.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #452818
Posted Thursday, February 7, 2008 10:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 7,064, Visits: 15,270
While I fully agree with Joe that it becomes unwieldy without the dates table - there IS an algorithm that calculates Easter....Thought you might be interested.


http://users.sa.chariot.net.au/~gmarts/eastalg.htm





----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #452844
Posted Thursday, February 7, 2008 11:09 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
Matt Miller (2/7/2008)
While I fully agree with Joe that it becomes unwieldy without the dates table - there IS an algorithm that calculates Easter....Thought you might be interested.


http://users.sa.chariot.net.au/~gmarts/eastalg.htm




Matt, While I also agree there is a SQL Holiday Algorithm referred to here. I think it is a corker...

http://www.sqlservercentral.com/Forums/Topic110259-23-1.aspx

Dave J



http://glossopian.co.uk/
"I don't know what I don't know."
Post #452857
Posted Thursday, February 7, 2008 11:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:15 PM
Points: 1,945, Visits: 3,008
[size="3"][font="Courier New"]>> While I fully agree with Joe that it becomes unwieldy without the dates table <<

I would say impossible because of civic decrees, which are unpredictable. And a 100 year table is really small by today's standards.

>> - there IS an algorithm that calculates Easter....Thought you might be interested. <<

Which Easter ?:) Here is the Greek Orthodox versus Roman Catholic and Protestant versions (please pardon the lack of ISO-8601 formatting on the dates):

WESTERN ORTHODOX
~~~~~~~ ~~~~~~~~
23 April 2000 30 April 2000
15 April 2001 15 April 2001
31 March 2002 5 May 2002
20 April 2003 27 April 2003
11 April 2004 11 April 2004
27 March 2005 1 May 2005
16 April 2006 23 April 2006
8 April 2007 8 April 2007
23 March 2008 27 April 2008
12 April 2009 19 April 2009

etc.

Now, I have a question. Is there an international standard for holidays? Some Dewey Decimal kind of thing that breaks them into civic, religious, etc. categories or something? I know the freighter ship industry has a list by country and ports of holidays, but that is all I have seen.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #452859
Posted Thursday, February 7, 2008 11:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 7,064, Visits: 15,270
It certainly is - but in both cases - it just goes to show how unwieldy it would get to calculate every single holiday based on some crazy set of rules. If you're going to go through all of that trouble - calculate it and store it (never mind the bizarre add-on rules, like "observe the holiday on the monday if it falls on a sunday, except if it's a minor holiday which might be defined by theses x rules in the HR handbook...")

And Joe's point is a good one. there are multiple definitions of a lot of these....But even just using the definitions your org has picked out, seems silly to have to figure them out over and over again.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #452861
Posted Wednesday, February 20, 2008 7:42 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
without disagreeing with any of the above, I got bit by having a "list of holidays" table yesterday.

We have a table, unimaginatively called ms_holiday.

The structure is similar to

CREATE TABLE [dbo].[MS_Holiday] (
[Holiday_Depot_No] [varchar] (7) COLLATE Latin1_General_CI_AI NOT NULL ,
[Holiday_Dt] [datetime] NOT NULL ,
[Holiday_Desc] [varchar] (40) COLLATE Latin1_General_CI_AI NOT NULL
) ON [SECONDARY]


(I snipped out the created_by, created_date, constraints etc.).

This stores holidays for each depot in our organization, so we could store Western Easter dates at out London depots, and Orthodox Easter dates at our Greek(?) Depots . ;)

But it breaks down when we add new depots and forget to populate the table with the new depots and their corresponding dates! [Yes, it was me, guilty as charged... ]

Therefore working things out on the fly for the times that these functions are called sounds a better bet for me.

Mind you, I could always go and beat up the developers of the app until they write a complete add new depot screen. (There are so many hacks I weep, regularly )

Dave J

Edited: Took out a typo or two. How come you never spot them in the preview, only after post?



http://glossopian.co.uk/
"I don't know what I don't know."
Post #457984
Posted Friday, March 20, 2009 6:52 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:38 AM
Points: 716, Visits: 464
Just to add another version - I used Jason's code and added a parm for number of days to add, plus or minus:

CREATE FUNCTION dbo.GetNextBusinessDay (@Date datetime,
@DaysToAdd int)
RETURNS datetime

/* Name: GetNextBusinessDay
*
* Purpose: Calculate business days from a particular date. @DaysToAdd can be positive or negative.
*
* Modification History:
* 03/20/2009 VRI Derived from http://www.sqlservercentral.com/Forums/Topic228794-253-2.aspx "Jason"
*
*/

AS
BEGIN

DECLARE @DayCounter int,
@Sign int

SET @DayCounter = 0

SET @Sign = SIGN(@DaysToAdd)

WHILE (@DayCounter < ABS(@DaysToAdd))
BEGIN

SET @Date = DATEADD(dd, @Sign, CONVERT(datetime, (CONVERT(CHAR(10), @Date, 101))))

WHILE ((@@DATEFIRST + DATEPART(dw, @Date) - 1) % 7) + 1 IN (1, 7)
OR @Date IN (SELECT HolidayDate
FROM Holiday)
SET @Date = DATEADD(dd, @Sign, @Date)

SET @DayCounter = @DayCounter + 1

END

RETURN @Date
END





Post #680344
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse