Add business days to a date field

Question

Post reply

Add business days to a date field

Viewing 5 posts - 16 through 19 (of 19 total)

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

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 1

jordon.shaw

Hall of Fame

Points: 3571

March 15, 2010 at 3:12 pm

#1134097

Great point! Thanks again!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

sturner (3/12/2010)
sure, shoot me a private message with an email address.

Nah... do here... in the open where everyone can learn.

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok this is providing a fun learning curve for me;

generating a calendar table's easy, but updating it to have holidays is tripping me up.

all the holidays that are based off of firsts, i have no problem generating:

--Presidents Day : third Monday of February

--Labor Day : first Monday in September

i can find the first monday, and add x number of days or weeks to get the right date...

my problem is finding the Last monday of a given month.

for example, i want Memorial day:

--Memorial Day : last Monday in May

i can find the last day of the month, but the last Monday/day of week has got me stumped;

I thought i'd use row number aganst the table to find all the mondays in september, but some years have rownumber=5 for the last monday, the others have rownumber=4;

here's my calendar example, and the code i used to get all the mondays for may over a bunch of years:

CREATE TABLE Calendar (

TheDate datetime not null primary key,

[DayOfWeek] varchar(50),

IsHoliday bit default 0,

IsWorkHoliday bit default 0,

HolidayName varchar(100) )

declare @NumberOfYears int

SET @NumberOfYears = 50 --x years back, and x years into the future

--ten years before and after todays date:

;WITH TallyCalendar AS

(

SELECT dateadd( dd,(-365 * @NumberOfYears) + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N

FROM (

SELECT TOP (730 * @NumberOfYears)

row_number() OVER (ORDER BY sc1.id) AS RW

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2

) X

)

INSERT INTO dbo.Calendar(TheDate,[DayOfWeek])

SELECT

TallyCalendar.N,

datename(dw,TallyCalendar.N)

FROM TallyCalendar

--find all mondays in may

select row_number() OVER (partition by year(TheDate),datename(dw,theDate) order by TheDate) As RW,* from calendar where month(theDate) = 5 and

datename(dw,theDate) = 'Monday'

i'm just not intuitively seeing how i would update my Calendar table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sturner SSC-Insane Points: 22411 More actions · Answer 4

The last Monday of the month is nothing more than the first Monday of next month minus 7 days.

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323518 More actions · Answer 5

[facepalm] oh snap!

so obvious when someone rubs your nose in it; thanks

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!