CODEWITHSUNDEEP
sqloracle-database
Konz Mama
Konz Mama

Reputation: 975

Counting the number of days excluding sunday between two dates

I am trying to calculate number of days betwen two dates excluding sundays. This is my query,

SELECT   F_PLANHM_END_DT
       - F_PLANHM_ST_DT
       -   2
         * (TO_CHAR (F_PLANHM_END_DT, 'WW') - TO_CHAR (F_PLANHM_ST_DT, 'WW'))
  FROM VW_S_CURV_PROC
 WHERE HEAD_MARK = 'IGG-BLH-BM 221';

SELECT COUNT (*)
  FROM (SELECT SYSDATE + l trans_date
          FROM (    SELECT LEVEL - 1 l
                      FROM VW_S_CURV_PROC
                CONNECT BY LEVEL <= ( (SYSDATE + 7) - SYSDATE)))
 WHERE TO_CHAR (trans_date, 'dy') NOT IN ('sun');

I am retrieving date from a view called VW_S_CURV_PROC with start date : F_PLANHM_ST_DT and end date F_PLANHM_END_DT. Somehow i cant make this to work. Please help me...

Upvotes: 2

Views: 925

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could use the ROW GENERATOR technique to first generate the dates for a given range, and then exclude the SUNDAYs.

For example, this query will give me the total count of days between 1st Jan 2014 and 31st Dec 2014, excluding the Sundays -

SQL> WITH DATA AS
  2    (SELECT to_date('01/01/2014', 'DD/MM/YYYY') date1,
  3      to_date('31/12/2014', 'DD/MM/YYYY') date2
  4    FROM dual
  5    )
  6  SELECT SUM(holiday) holiday_count
  7  FROM
  8    (SELECT
  9      CASE
 10        WHEN TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=AMERICAN') <> 'SUN'
 11        THEN 1
 12        ELSE 0
 13      END holiday
 14    FROM data
 15      CONNECT BY LEVEL <= date2-date1+1
 16    )
 17  /

HOLIDAY_COUNT
-------------
          313

SQL>

Upvotes: 3

Related Questions