Business Days between two dates

system · May 24, 2012, 2:39pm

How can I calculate business days between two dates ?

Developer_Designer (BOB member since 2009-08-09)

MarkP · May 24, 2012, 2:51pm

I’d recommend a calendar table with a business day flag that’s a 1 for working days and 0 for non-working days.

system · May 24, 2012, 3:02pm

Mark,

Thanks. Where do I get calendar table ? Will it also take care of holidays other than SAT and SUN ?

Please advise.

Developer_Designer (BOB member since 2009-08-09)

system · May 24, 2012, 3:10pm

You will have to create a calendar table and manually mark the holidays and weekends as non business days. You need to use the organization’s calendar.

Jansi (BOB member since 2008-05-12)

MarkP · May 24, 2012, 3:11pm

You need to build one!

Sample scripts are available (via Google) for your particularly database (SQL Server, Oracle, etc.)

What you need to provide is public holidays for your country (some are again available via SQL scripts on Google)

You would then build this into your universe and can work out all sorts of things with it - attendance percentages, business hours productivity and so on.

system · May 24, 2012, 3:25pm

“Business Days” is one of those simple sounding requests that is a a real bear to implement.

In multinational company, holidays can differ from country to country or division to division. Is Boxing Day a holiday? Mark and I would give you different answers. So your first step is to identify which national, local, religious and other holidays count.

As Jansi and Mark P have indicated the calendar table is the place to implement this. There are lots of other nice things you can do with a calendar table.

Tom Thompson (BOB member since 2003-06-04)

system · May 24, 2012, 3:27pm

Sounds very complex to me. Database is Teradata. Country is USA. Standard government holidays along with Sat and Sun.

I guess I need to create a Derive table. I was looking for the codes in google. I would really appreciate if anyone has it ready.

Developer_Designer (BOB member since 2009-08-09)

MarkP · May 24, 2012, 3:30pm

Absolutely not!

You need to create a physical table in the database. Not used Teradata for 15 years, so don’t have anything to help with that. You could try having a search for calendar table on a Teradata forum.

system · May 24, 2012, 3:44pm

Thanks Mark for the quickest response. I am not sure whether I am explaining correctly or not.

I have to calculate the business days difference in the following two cases:

Days difference = Actual PGI Date - Plan PGI Date
Days Difference = Today - Actual GI Date

Actual PGI Date and Plan PGI Date come from two different tables.

Developer_Designer (BOB member since 2009-08-09)

MarkP · May 24, 2012, 3:48pm

Yes. We know what you’re after and the best (and easiest) way to do it is with a calendar table that reflects your company’s non-working days. Vendors don’t build these because they are highly specific to an organisation - some in the UK choose to close down between Christmas and new year for example.

system · May 25, 2012, 6:16pm

Hi,

Dave’s blog might help too.
http://www.dagira.com/2009/10/23/calculating-business-days-between-two-days-via-report-functions/

M H Mohammed (BOB member since 2010-06-16)

system · November 8, 2012, 9:56pm

I agree with Mark P, don’t underestimate the complexity of this requirement. Here in Australia each state has different holidays. If an activity begins in Sydney and finishes in Melbourne, which public holidays should I count?

Make sure you know what you want to do with issues like this before deciding on a solution.

In the meantime, this:

=DaysBetween (RelativeDate([Order Date];
Floor(DayNumberOfWeek([Order Date])/6)*(8-DayNumberOfWeek([Order Date])));RelativeDate([Ship Date];
Floor(DayNumberOfWeek([Ship Date])/6)
*(8-DayNumberOfWeek([Ship Date]))))-(Floor(DaysBetween(RelativeDate(RelativeDate([Order Date];
Floor(DayNumberOfWeek([Order Date])/6)*(8-DayNumberOfWeek([Order Date])));
(DayNumberOfWeek(RelativeDate([Order Date];
Floor(DayNumberOfWeek([Order Date])/6)
*(8-DayNumberOfWeek([Order Date]))))+1)*-1);
RelativeDate([Ship Date];
Floor(DayNumberOfWeek([Ship Date])/6)
*(8-DayNumberOfWeek([Ship Date]))))/7)*2)

Will count the weekdays between an Order Date and a Ship Date ignoring public holidays.

Explanation of the logic is here: http://blog.davidg.com.au/2012/11/workdays-between-two-dates-in-webi.html

[Moderator Edit: Added code formatting - Andreas]

David Gilbertson (BOB member since 2011-03-08)