6 Replies Latest reply: Sep 10, 2013 3:48 AM by chris227 RSS

    count of working days for current month as of given date

    JV123

      Hi,

       

      how can I get count of working days as of given date using SQL? appreciate for your help. thanks.

        • 1. Re: count of working days for current month as of given date
          Frank Kulash

          Hi,

           

          That depends on what a "working day" is.

          If a working day is any day except a Saturday or Sunday, then

           

          SELECT  COUNT (*) AS num_days

          FROM    dual

          WHERE   TO_CHAR ( TRUNC (SYSDATE, 'MONTH') + LEVEL - 1

                          , 'Dy'

                          , 'NLS_DATE_LANGUAGE=ENGLISH'

                          )  NOT IN ('Sat', 'Sun')

          CONNECT BY  LEVEL <= EXTRACT ( DAY FROM LAST_DAY (SYSDATE) )

          ;

          To get the working days in a given month (not necessarily the current month) you can use any DATE instead of SYSDATE; just be sure to use the same DATE in both places in the query.

          • 2. Re: count of working days for current month as of given date
            chris227

            select

            sum(

              case when

                      mod(to_number(

                            to_char(trunc(sysdate)+level+1,'J')

                      ),7)

                     between 1 and 5 then 1 end)

            not_weekend

            from dual

            connect by

            level <= last_day(sysdate) - trunc(sysdate)

            • 3. Re: count of working days for current month as of given date
              JV123

              Hi,

               

              Business days logic is part of this query but it is giving an error "ORA-00936: missing expression"

               

               

              SELECT GLP.period_set_name,

                GLP.period_name,

                GLP.period_set_name

                || '~'

                || GLP.period_name integration_id,

                (

                (CASE

                  WHEN TO_CHAR(GLP.END_DATE,'MON-YYYY')=TO_CHAR(SYSDATE,'MON-YYYY')

                  THEN

                  SELECT COUNT(1)

                  FROM

                    (SELECT start_date + level-1

                    FROM

                      (SELECT start_date,

                        end_date,

                        end_date-start_date+1 interval

                      FROM GL_PERIODS GLP

                      WHERE 1=1

                      AND sysdate BETWEEN start_date AND end_date

                      AND GLP.period_set_name        = 'CMP_CALENDAR'

                      AND GLP.adjustment_period_flag = 'N'

                      )

                    WHERE start_date       + level-1          <= sysdate

                    AND TO_CHAR(start_date + level-1,'D') NOT IN (1,7)

                      CONNECT BY level                        <= interval

                    )

                  WHEN TO_CHAR(GLP.END_DATE,'MON-YYYY')  <> TO_CHAR(SYSDATE,'MON-YYYY')

                  AND (GLP.end_date - GLP.start_date + 1) = 35

                  THEN 25

                  ELSE 20

                END) - NVL(xcpt.holiday_count,0)BUSINESS_DAYS

                FROM GL_PERIODS GLP,

                  (SELECT GLP.period_name,

                    COUNT(*) holiday_count

                  FROM BOM_CALENDAR_EXCEPTIONS bce,

                    GL_PERIODS GLP

                  WHERE bce.exception_date BETWEEN GLP.start_date AND GLP.end_date

                  AND GLP.period_set_name IN('CMP_CALENDAR','CMP_WEEKLY_CAL')

                  AND bce.calendar_code    = '431'

                  GROUP BY GLP.period_name

                  ) xcpt

                WHERE GLP.period_set_name     IN('CMP_CALENDAR','CMP_WEEKLY_CAL')

                AND GLP.adjustment_period_flag = 'N'

                AND GLP.period_name            = xcpt.period_name

                AND GLP.END_DATE              <=SYSDATE

              • 4. Re: count of working days for current month as of given date
                Frank Kulash

                Hi,

                 

                Whenever you have a problem, please post whatever is required for people to re-create the problem and test their ideas.  In this case, that includes CREATE TABLE and INSERT statements for any tables needed (just a few rows, relevant columns only) and the results you want from that data.

                 

                The syntax for a scalar sub-query (that is, a query that takes the place of a single value, as in a CASE expression) is

                 

                (

                    SELECT  ...

                    FROM    ...

                )

                that is, the entire query must be inside parentheses that enclose nothing else.  It looks like you have the keyword SELECT in a CASE expression without an opening left parenthesis right before it.

                • 5. Re: count of working days for current month as of given date
                  Rahul_India
                  with t as (
                  SELECT TO_CHAR(TRUNC(SYSDATE ,'MON') + LEVEL -1,'DAY') as c FROM DUAL
                  WHERE TO_CHAR(TRUNC(SYSDATE ,'MON') + LEVEL -1,'DAY') NOT IN ('SUNDAY','SATURDAY')
                  CONNECT BY LEVEL <=
                  LAST_DAY(SYSDATE) - TRUNC(SYSDATE,'MON') +1 )
                  
                  
                  SELECT count(*) from t
                  where c NOT IN ('SUNDAY   ','SATURDAY ');
                  
                  • 6. Re: count of working days for current month as of given date
                    chris227

                    One remark:

                     

                    Be aware that the to_char(date) function is NLS-setting dependend. That means the start of the week may vary across that settings.

                    That's why Frank used the extra nls-paramter and i the julian date format.

                    But i guess, that doesnt play any role in your case.