Calculate number of Business Days

[Former Member]

I need to calculate number of Business Days in BODS. Lets say I have One source table and One Date Dimension table to identify Business Day and need to calculate the no of working days and load in to FACT table (Like in screenshot below)?

By adding Query transform, I can calculate no of Days, but how to calculate working days between ORDER_RECEIVED_DATE and ORDER_SHIPPED_DATE.

SQL: select count(*) from DIM_DATE  where DATE between ORDER_RECEIVED_DATE and ORDER_SHIPPED_DATE and HOLIDAY_FLAG='NO'

[former_member187605 · Accepted solution]

Add an extra column to DIM_DATE,  WORKING_DAY_NO, to contain an ever increasing sequence number that counts the number of working days since the beginning of your calendar. WORKING_DAY_NO can be easily populated with a call to a function f_working_days(HOLIDAY_FLAG) in its mapping:

     if ($HOLIDAY_FLAG='NO') 
       begin
       $G_working_day = $G_working_day + 1;
       end

     return ($G_working_day);

$G_working_day is a global variable, initialised to 0.

Calculating the number of business days between ORDER_RECEIVED_DATE and ORDER_SHIPPED_DATE has become a piece of cake, now:

lookup(DIM_DATE, WORKING_DAY_NO, 0, 'PRE_LOAD_CACHE', DATE , ORDER_RECEIVED_DATE) - lookup(DIM_DATE, WORKING_DAY_NO, 0, 'PRE_LOAD_CACHE', DATE , ORDER_SHIPPED_DATE)

[Former Member · Accepted solution]

Dirk, Thanks for suggestion, I saw similar concept in other ETL tool, will try myself.

Edit: Dirk, Got chance to try your solution, its simple to implement and best in performance, thanks a lot.

[Former Member · Accepted solution]

Hello,

This solution is very appreciated for us.

Thanks so much.

Manuel

[former_member186897]

Hi Prashanth Chinta,

You can use below function to calculate the no. of days:

date_diff(ORDER_RECEIVED_DATE, ORDER_SHIPPED_DATE,'D')

Now you need to subtract the no. of holidays which comes between these two dates then you are done. And to calculate the no. of holidays you need to have all the dates between these two dates which can be joined with DIM_DATE table to count the days for holidays.

Hope this helps.

Regards,

Ansari MS

[Former Member]

Ansari,

If I understand correct, Dataflow looks something like below, after Qry_Lkp, I have 30 columns including,

ORDER_SHIPPED_DATE, ORDER_RECEIVED_DATE, NO_OF_DAYS. Now I joined DIM_DATE on DIM_DATE.DATE between ORDER_RECEIVED_DATE and ORDER_SHIPPED_DATE on Qry_GrpBy. Here is my question, now if i do add one more column to calculate Holidays in Qry_GrpBy, lets say NO_OF_HOLIDAYS: what should be the formula? so I can add one more Query transform and do NO_OF_DAYS - NO_OF_HOLIDAYS to get Business Days.

If you say something like count(), then I need to add remaining 29 columns in Group by clause right?

Is there any alternate way....

[former_member186897]

Prashanth Chinta,

I have simulated this Job in my repository and and below are the steps by step implementation.

Your Dataflow will look like below. It contains the source and Target table with expected results. Hope this is what you are looking for.

DIM_DATE table contains all the dates in a year and mark each date either Holiday as 'YES' or 'NO'

Qry_Total_Ship_Days Query Transform

Qry_Join Query Transform

Qry_Filter_Holidays Query Transform

Qry_Cnt_Holidays Query Transform

Qry_Cnt_Holidays Query Transform (This is to show the groupby columns)

Qry_Cal_Ship_Days Query Transform

[Former Member]

Ansari,

Thanks for your time, I did similar like yours, except to overcome Group by on 29 columns and duplicate Dates, I added 2 extra Qry Transforms like in screenshot and calculated Holidays separately. My fact table grain is Product, due to duplicate dates, count(*) might give wrong results. But I got the idea from yours and thanks for bonus tip (Date_Diff() + 1).

Loaded data successfully, created Universe, users happily testing in BI.

[former_member205900]

Hi All,

I need your help on below scenario:

Example:

In Soucre file

EMP ID      DateField

29               21/02/2014

29               21/04/2015

30               22/09/2013    

30               22/07/2014

30               22/09/2014

OutPut

EMP ID      DateField               DateSeq

29               21/02/2014               1    

29               21/04/2015               1   

30              22/09/2013                1

30               22/07/2014                1   

30              22/09/2013                2  

Please Suggest me.

Regards

Varsha

[Former Member]

To get working days, you may have to use a SQL Query like show in the link with case statement - sql server 2005 - Business Days calculation - Stack Overflow

[Former Member]

I can do the same by using the sql I posted, but without using sql transfer, is there any simple way to do the same, can you elaborate more on case statement in this scenario, please?

[Former Member]

You don't need to do a SQL transfer, write a stored procedure and call it as a function in the Query transform.

Some thing like this from google -

CREATE FUNCTION "fnGetBusinessDays"

(

@startdate datetime,

@enddate datetime

)

RETURNS integer

AS

BEGIN

DECLARE @days integer

SELECT @days =

DATEDIFF(d,@startdate,@enddate)

- DATEDIFF(wk,@startdate,@enddate) * 2

- CASE

WHEN DATENAME(dw, @startdate) <> 'Saturday' AND DATENAME(dw, @enddate) = 'Saturday' THEN 1

WHEN DATENAME(dw, @startdate) = 'Saturday' AND DATENAME(dw, @enddate) <> 'Saturday' THEN -1

ELSE 0

END

- (SELECT COUNT(*) FROM holidays WHERE bankholiday BETWEEN @startdate AND @enddate AND DATENAME(dw, bankholiday) <> 'Saturday' AND DATENAME(dw, bankholiday) <> 'Sunday')

RETURN (@days)

END

GO