Is it possible to exclude Weekends for Days open calculation in BI reporting?

Question

Summary:

Content (required):

Hi,

We have a requirement to identify the Requisitions which are posted internally for 7 Business days (Excluding weekends and Holidays). Is there a way to calculate Business days alone using SQL query?  I know the holidays can be identified using calendar tables, but we are stuck with finding ways to exclude weekends in our query.

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

Joshua C. Stewart · Answer

Hi @Abhishek Jois,

This can be handled if you have a proper calendar dimension associated with your data model. Most calendar dimensions will have a column CAL_DAY_OF_WK, which correspond to the numerical day of the week. You could either extend your calendar dimension to include a weekday flag by generating a logical column where CAL_DAY_OF_WK NOT IN (7,1) or use a filter in your reporting layer with the same logic.

Venkat Koduvayur-Oracle · Answer

Hi Abhishek

You need to replace the D_DAY with your actual time dimension table.  This table is given for reference  purpose.

If you are using FAW, then I think the table is W_DAY_D.  Pls give a try

Regards

Venkat

Abhishek Jois · Answer

Hi Venkata,

D_DAY is invalid table and im getting error when i run this query. Is this query working for you?

Thanks,

Abhishek.