Calculating Work Days

Question

Calculating Work Days

Viewing 15 posts - 106 through 120 (of 156 total)

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

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

Yes... short answer would be to use a calendar table to get the smallest non-holiday, non-weekend date that is >= 15 days out.

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

Actually, I figured it out about an 30 minutes before the end of the day today.

SET @StartDate = getdate()

SET @EndDate = @StartDate + 15

SET @BizDays15 = (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))

While @BizDays15 <= 15

Begin

@EndDate = @EndDate + 1

SET @BizDays15 = (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))

END

I did the same thing with the 3 business day. While I hate the RBAR, I can consolidate myself with the fact that I have at max 6 iterations for 15 business days, and 2 iterations for 3 business day. Still significantly higher performance than other routes I have seen. Thanks again for everything.

Fraggle

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Heh... cool! But, you don't really hate RBAR... or you wouldn't be using it. 😉

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

No, I hate it, but there are times where 1) you can use nothing else or 2) you are on such a time crunch, you do it to get it done until you can figure out another way. I am still looking for another way, but until then, I atleast got a solution.

Fraggle

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Heh... dang managers all need to take a course in how to effectively plan and schedule a software project for success. 😉

Anyway, glad it does what you want and the performance isn't too bad.

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

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

Jeff -- I noticed your:

"Yup, I know about BETWEEN… that would be the subject of whole different article."

and wondered if you ever addressed that more. (I'll admit I didn't get through all 12 pages of the comments to your post.)

I have always assumed (perhaps incorrectly) that the difference was simply syntax, and now I wonder if I should pay more attention.

(As usual, I stand in awe of the contributions you make here. Thanks!)

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

Hi Jim,

Thanks for the feedback.

It's amazing, to me, how many posts this article elicited... and it was my very first article, to boot! 🙂

As you probably already know, the following are equivalent...

c BETWEEN a AND b

c >= a AND c <= b

No big suprises and, as you say, it's just a matter of syntactical preference... except when it comes to things like dates. I've seen all sorts of gyrations with dates when trying to include whole dayswith times including the incredulous...

b BETWEEN a AND a +23:59:59.999

... which, of course and due to rounding, will NOT give the desired results. Even the more proper but still subject to error...

b BETWEEN a AND a +23:59:59.997

... is less effective than the correct alternative and, depending on the datatype, could still produce incorrect results in 2008.

The correct way to look for a single whole day on a datetime column is...

b >= a AND b < a+1

Of course, that's only if "a" is a datetime with a midnight value or what people call a whole date or "date with no time". You should see some of the gyrations people go through to make that happen to "a".

Bottom line is that using BETWEEN on DateTime datatypes might mean you accidently include many of the entries on the next day or you miss many of the entries on the current day.

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

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

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

Heh... thanks Fraggle... I guess you really do hate RBAR after all! 🙂

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

Fraggle-805517

SSCrazy Eights

Points: 8907

October 24, 2008 at 8:06 pm

#889588

No fruity loops in my database please. 🙂

aevans1981 SSC Rookie Points: 35 More actions · Answer 11

First, thanks for a great function, however...

I have read through most of this post, but not all, I am using the code that was originally supplied.

When I try fn_WorkDays(27/10/2008 00:00:00, 27/10/2008 00:00:00) it returns 1, shouldnt this be 0?

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

aevans1981 (11/4/2008)
First, thanks for a great function, however...
I have read through most of this post, but not all, I am using the code that was originally supplied.
When I try fn_WorkDays(27/10/2008 00:00:00, 27/10/2008 00:00:00) it returns 1, shouldnt this be 0?

It's mostly a personal or business requirements choice... if your requirements are that today only is a full day, then it should return a 1. If your requirements are that today should not be counted, then it should return a 0. The fix to do that is quite simple (and could easily be parameterized)... just change the following...

--Start with total number of days including weekends

(DATEDIFF(dd,@StartDate,@EndDate)[font="Arial Black"]+1[/font])

... to...

SELECT

--Start with total number of days including weekends

(DATEDIFF(dd,@StartDate,@EndDate))

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

aevans1981 SSC Rookie Points: 35 More actions · Answer 13

Thanks for that Jeff, that worked great.

I have commented out the swap section as i am using this to calculate the time taken to turn around orders from when we receive them, which may result in negative figures.

I am now getting wrong days returned when using

fn_WorkDays(ReceiveOrder, OrderRequired)

fn_WorkDays(27/10/2008 00:00:00, 23/10/2008 00:00:00)

returns -2, shouldnt this be -3, they have given us -3 working days to supply this order?

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

aevans1981 (11/5/2008)
Thanks for that Jeff, that worked great.
I have commented out the swap section as i am using this to calculate the time taken to turn around orders from when we receive them, which may result in negative figures.
I am now getting wrong days returned when using
fn_WorkDays(ReceiveOrder, OrderRequired)
fn_WorkDays(27/10/2008 00:00:00, 23/10/2008 00:00:00)
returns -2, shouldnt this be -3, they have given us -3 working days to supply this order?

I believe the easist thing to do would be to put the swap section back in with a "caveat chaser"... start a variable at "1"... if a swap occurs, change it to a -1. Either way, multiply the number of days times that variable.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 15

aevans1981 (11/4/2008)
First, thanks for a great function, however...
I have read through most of this post, but not all, I am using the code that was originally supplied.
When I try fn_WorkDays(27/10/2008 00:00:00, 27/10/2008 00:00:00) it returns 1, shouldnt this be 0?

If you leave for a business trip on 27/10/2008 and return on 27/10/2008 - how many days you spend on business trip?

Ask your accounting department, see what they will say.

_____________
Code for TallyGenerator