SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding the Next Business Day Recursively


Finding the Next Business Day Recursively

Author
Message
Joe Christley
Joe Christley
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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.



Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12637 Visits: 18584
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?
David Jackson
David Jackson
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1913
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."
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12637 Visits: 18584
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?
David Jackson
David Jackson
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1913
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 . Wink

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... Blush]

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 Sad)

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."
vince.iacoboni@db.com
vince.iacoboni@db.com
SSC Eights!
SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)SSC Eights! (951 reputation)

Group: General Forum Members
Points: 951 Visits: 552
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






Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search