Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
680
Views
4
Comments
Abhishek Jois
Abhishek Jois Rank 1 - Community Starter
edited Aug 13, 2024 7:49PM in Oracle Fusion Data Intelligence

Summary:

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

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

Tagged:

Answers

  • Venkat Koduvayur-Oracle
    Venkat Koduvayur-Oracle Rank 4 - Community Specialist

    Hi

    Below query snippet will help to identify the days excluding weekends.

    D_DAY is the time dimension at day level.

    You need to pass requisition creation date as 1-Jan-2022 and requisition end date as 15-Jan-22 in the query as commented below to calculate requisition posted days. You need to replace them with actual db column during the calculation logic.

    This query assume the weekend is on SAT and SUN.


    SELECT COUNT(DAY_DT) -- REQUISITION_POSTED_DAYS

    FROM D_DAY

    WHERE DAY_DT BETWEEN '01-JAN-2022' --REQUISITION_CREATE_DT

    AND '15-JAN-2022' --REQUISITION_END_DT

    AND TO_CHAR(DAY_DT,'DY', 'NLS_DATE_LANGUAGE = AMERICAN') NOT IN ('SAT','SUN')


    Regards

    Venkat

  • Abhishek Jois
    Abhishek Jois Rank 1 - Community Starter

    Hi Venkata,


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


    Thanks,

    Abhishek.

  • Venkat Koduvayur-Oracle
    Venkat Koduvayur-Oracle Rank 4 - Community Specialist

    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

  • Joshua C. Stewart
    Joshua C. Stewart Rank 5 - Community Champion

    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.