Forum Stats

  • 3,770,491 Users
  • 2,253,125 Discussions
  • 7,875,485 Comments

Discussions

Reg: Count of non working/business days between 2 dates

User_JZKI6
User_JZKI6 Member Posts: 38 Green Ribbon

Hi Team,

We have 2 dates (Start_date and End_date), need to calculate count of non-business days (Sunday and Saturday) between these 2 dates. Can someone assist how to achieve this.

Thanks in Advance,

Tagged:

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond
    edited May 3, 2021 4:36PM
    SELECT  COUNT(*)
      FROM  DUAL
      WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
      CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
    /
    

    For example weekdays in May 2021:

    DEFINE START_DATE="DATE '2021-05-01'"
    DEFINE END_DATE="DATE '2021-05-31'"
    SELECT  COUNT(*)
      FROM  DUAL
      WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
      CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
    /
    old   3:   WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
    new   3:   WHERE (DATE '2021-05-01' + LEVEL - 1) - TRUNC(DATE '2021-05-01' + LEVEL - 1,'IW') < 5
    old   4:   CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
    new   4:   CONNECT BY DATE '2021-05-01' + LEVEL - 1 <= DATE '2021-05-31'
    
    
      COUNT(*)
    ----------
            21
    
    
    SQL>
    
    

    SY.

  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    You can also try this:

    select least(7-to_char(:p_start_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+1,2)

    +greatest(to_char(:p_end_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')-5,0)

    +((trunc(:p_end_date,'IW')-trunc(:p_start_date,'IW'))/7-1)*2 as cnt

    from dual

    cormacoUser_JZKI6
  • User_JZKI6
    User_JZKI6 Member Posts: 38 Green Ribbon
    edited May 4, 2021 11:16AM

    Thank you for all your replies, this worked for me, our main task is to find the no. of business day’s between 2 dates (Start_date and End_date) that should exclude weekends (non-working days) and public holidays. 

    Initially we took the actual diff between 2 dates, for the results we excluded the count of weekends, now how we can find the count of public holidays if we exclude with the result then we will get actual business days count.

    We have time table having holiday_flag (‘Y’ or ‘N’) field how we can make of this and find the count of holidays any suggestions.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond

    We have time table having holiday_flag (‘Y’ or ‘N’) field how we can make of this and find the count of holidays any suggestions.

    It would be simpler to have non-working day table with both weekends (say W) and holidays (say H). Anyway, I'll assume your time table has column DT:

    SELECT  COUNT(*)
      FROM  DUAL
      WHERE (&&START_DATE + LEVEL - 1) - TRUNC(&&START_DATE + LEVEL - 1,'IW') < 5
        AND &&START_DATE + LEVEL - 1 NOT IN (SELECT DT FROM TIME_TABLE WHERE HOLIDAY_FLAG = 'N')
      CONNECT BY &&START_DATE + LEVEL - 1 <= &&END_DATE
    /
    

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond

    Actually, it isn't clear what data you store in time table. If it does store all dates then:

    SELECT  COUNT(*)
      FROM  TIME_TABLE
      WHERE DT BETWEEN &&START_DATE AND &&END_DATE
        AND DT - TRUNC(DT,'IW') < 5
        AND HOLIDAY_FLAG = 'N'
    /
    

    SY.

  • User_JZKI6
    User_JZKI6 Member Posts: 38 Green Ribbon

    Actually we have 2 tables Order_Start_date and Order_End_date are from Order table and holidayflag ('Y','N'') and weekend('Y','N') flag are from Time table.

    Here we are joining the both tables as below

    from order A ,time B

    B. Time_Key between B. Order_Start_date and B. Order_End_date

    Trying to find the no. business days using above 2 tables it should exclude the weekends(non-working days) and public holidays 

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond

    Still not clear. I'll assume table TIME has all dates (column DT) and two flag columns HOLIDAY and WEEKEND and you trying to find number of workdays for each order. If so:

    SELECT  O.*,
            (
             SELECT  COUNT(*)
               FROM  TIME T
               WHERE T.DT BETWEEN O.ORDER_START_DATE AND ORDER_END_DATE
                 AND T.HOLIDAY = 'N'
                 AND T.WEEKEND = 'N'
            ) ORDER_WORKDAY_COUNT
      FROM  ORDER O
    /
    

    SY.