Calculating Work Days

Question

Calculating Work Days

Viewing 15 posts - 121 through 135 (of 156 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Fraggle (10/24/2008)
Jeff,
Alright, now that I have had a little time to ponder the imponderable as my dad would say, I figured out how to add X number of business days to a start date.
Create Function fn_AddBusinessDays
(@StartDate DATETIME,
@DaysToAdd TINYINT
)
Returns DATETIME
AS
BEGIN
DECLARE @EndDate DATETIME
SET @EndDate = DateAdd(Week, @DaysToAdd/5, @StartDate)
+ CASE
WHEN DatePart(dw, @StartDate) + @DaysToAdd % 5 >= 7
THEN @DaysToAdd % 5 + 2
ELSE @DaysToAdd % 5 --else justadd the day
END
RETURN CASE
WHEN DATENAME(dw, @EndDate) = 'Saturday'
THEN DateAdd(dd, 2, @EndDate)
WHEN DATENAME(dw, @EndDate) = 'Sunday'
THEN DATEADD(dd,1,@EndDate)
ELSE @EndDate
End
END
See what a little time will do. No RBAR!
Fraggle

Hey there, Fraggle... I know it's been a while, but I thought that I'd tell you that not only did I test your good function, but I've used it quite a few times. The cool thing about it is that you can easily use it on a whole bunch of dates. When you trying to do the same thing with a calendar table, you have to do a subquery with something like ...

SELECT MIN(calen_dt) FROM dbo.Calender WHERE calen_dt >= DATEADD(d, @intErval, @dteCurrentDate) AND flg_bdate='Y'

Now, I'll admit that your function doesn't handle holiday's like a calendar table might... but if you had a nice holiday table...

Anyway, thanks again for the function.

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

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 2

Jeff,

Glad to hear that you are getting some use out of it. It get's used about 30-40 times a day to update work tickets in our system.

Me personally, I would just do a lookup on the Calendar table to find out if the day is a Holiday. If performance was bad, I would try a non-clustered index and see if that helps at all. (year, month, day, holiday). Could actually do some logic to verify you are actually in a month with a holiday (no holiday in June).

Fraggle

Jay Taylor-604520 Old Hand Points: 350 More actions · Answer 3

These days, with furloughs a popular tool for profitability, a flexible approach is necessary.

Holidays have to be managed by the company, not just by logic. I think a two-step process is in order.

Start by importing from a web service or other source the holidays your company observes. http://www.holidaywebservice.com/holidays/HolidayService.asmx might be suitable.

Do it for a 50- or 100-year period, whatever suits.

Edit these if necessary. This gives you a start.

Then, row by agonizing row, add the special holidays or furloughs your company has observed and plans to observe. Or, if you plan to offload this to somebody in HR, create an interface.

Keep the list in a table or in an xml file.

Whichever way you do your calculations, consult this list.

Madhu Bh Grasshopper Points: 21 More actions · Answer 4

Ramesh Lende (1/10/2006)
I know everyone is talking about Holidays For this particular function. but guys don't worry to consider holidays in this is very easy. I have modified this SQL little bit and it would take care of holidays also, even though holiday comes on weekend.
First Create Holiday Table
create table HolidayTable (HolDay datetime)
Insert two Holidays in it. (One on weekday and one on weekend just to
make sure function does consider weekend holidays)
insert into HolidayTable values ('01/03/2006')
insert into HolidayTable values ('01/08/2006')
Now change SQL to
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- ( Select count(*) from HolidayTable where HolDay between @StartDate and @EndDate and
(datepart(dw,HolDay) <> 1 and datepart(dw,HolDay)<>7 ))
and you are ALL SET.
Hope this helps,
Best Regards,
Ramesh.

Madhu Bh Grasshopper Points: 21 More actions · Answer 5

Madhu Bh

Grasshopper

Points: 21

February 18, 2009 at 3:40 am

#945773

It was really fantastic thank you so much....

kgayda SSCrazy Points: 2157 More actions · Answer 6

Excellent post Jeff. I had written a function to calculate business days about 3 1/2 years ago for a client of mine (http://www.sqlservercentral.com/scripts/Miscellaneous/31543/). Yours is a bit more elegant. I had to have a variable work week parameter and include holidays that were stored in a holiday table. I couldn't think of a way at the time to get an accurate answer for every scenario other than the solution that I implemented. I may have to refactor my original to be non-RBAR using the technique that you demonstrated.

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

Thanks for the feedback, Karen. I know there's a lot of them, but some of the posts in the discussions in this thread show some pretty nifty ways to handle the problem, as well.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Lynn Pettis (2/19/2009)
I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.

Dang... sorry I missed this. Thanks for the feedback, Lynn.

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

bindu.muttavarapu Valued Member Points: 59 More actions · Answer 10

Hi,

Can any one help in calculating wordays starting from monday to friday

right now I am using (DATEDIFF(DAY, DATES.StartDate, DATES.EndDate)+1) to get monday to sat but

what to do to get from monday to friday ..

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

bindu.muttavarapu (3/6/2009)
Hi,
Can any one help in calculating wordays starting from monday to friday
right now I am using (DATEDIFF(DAY, DATES.StartDate, DATES.EndDate)+1) to get monday to sat but
what to do to get from monday to friday ..

I believe you may have missed the article this thread is attached to... please see the following...

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

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

dbajunior SSCrazy Points: 2307 More actions · Answer 12

Just tried this out....good job!

Or in SoCal parlance: "Dude, you rock!":cool:

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 13

James A. Lawrence (5/4/2009)
Just tried this out....good job!
Or in SoCal parlance: "Dude, you rock!":cool:

Great to hear, James. Thanks for taking the time to post a reply. 🙂

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 14

Lynn Pettis (2/19/2009)
I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.

I don't know how I missed this, Lynn. Thanks for the feedback.

{EDIT} Wow! Deju vu, huh? More coffee, please.

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

wim.buyens Ten Centuries Points: 1159 More actions · Answer 15

I calculate wokringdays like this:

SELECT days/7*5 + days%7

- CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END

- CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END

FROM (SELECT

DATEDIFF(day, @StartDate, @EndDate) + 1 AS days,

DATEPART(weekday, @StartDate + @@DATEFIRST - 1) AS wd

) AS D)