sql oracle ignore holidays

I am using this code to calculate the difference between two dates ignoring weekends:

SELECT To_date(SYSDATE) - 
      To_date('01.07.2014', 'DD.MM.YYYY') 
      - 2 * ( TRUNC(Next_day(To_date(SYSDATE) - 1, 'FRI')) 
      - TRUNC( Next_day(To_date('01.07.2014' , 'DD.MM.YYYY') 
      - 1, 'FRI')) ) / 7 AS DAYS_BETWEEN
FROM dual

I have another table called table1 in which the column "date" exists (its type is "DATE") in which all dates where a holiday is are written down.

Example table 1:

DATES

12.06.2011
19.06.2014
09.05.2013
...

I am trying to make my code check this table and that if one date is between the two dates above it makes -1 day in the output.

回答1:

It should be easy if you divide it into following tasks:

Generate all the dates between the two given dates using Row Generator method as shown here.
Ignore the dates which are weekend, i.e. Saturdays and Sundays
Check whether the dates in the range are having any match in the holiday table.

The following row generator query will give you the total count of weekdays, i.e. not including Saturdays and Sundays:

SQL> WITH dates 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(weekday) weekday_count
  7  FROM
  8    (SELECT
  9      CASE
 10        WHEN TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=AMERICAN')
 11             NOT IN ('SAT', 'SUN')
 12        THEN 1
 13        ELSE 0
 14      END weekday
 15    FROM dates
 16      CONNECT BY LEVEL <= date2-date1+1
 17    )
 18  /

WEEKDAY_COUNT
-------------
          261

SQL>

Now, based on above row generator query, let's see a test case.

The following query will calculate the count of working days between 1st Jan 2014 and 31st Dec 2014 excluding the holidays as mentioned in the table.

The WITH clause is only to use it as tables, in your case you can simply use your holiday table.

SQL> WITH dates
  2       AS (SELECT To_date('01/01/2014', 'DD/MM/YYYY') date1,
  3                  To_date('31/12/2014', 'DD/MM/YYYY') date2
  4           FROM   dual),
  5       holidays
  6       AS (SELECT To_date('12.06.2011', 'DD.MM.YYYY') holiday FROM   dual UNION ALL
  7           SELECT To_date('19.06.2014', 'DD.MM.YYYY') holiday FROM   dual UNION ALL
  8           SELECT To_date('09.05.2013', 'DD.MM.YYYY') holiday FROM   dual),
  9       count_of_weekdays
 10       AS (SELECT SUM(weekday) weekday_count
 11           FROM   (SELECT CASE
 12                            WHEN To_char(date1 + LEVEL - 1, 'DY',
 13                                 'NLS_DATE_LANGUAGE=AMERICAN')
 14                                 NOT IN (
 15                                 'SAT',
 16                                 'SUN' ) THEN 1
 17                            ELSE 0
 18                          END weekday
 19                   FROM   dates
 20                   CONNECT BY LEVEL <= date2 - date1 + 1)),
 21       count_of_holidays
 22       AS (SELECT Count(*) holiday_count
 23           FROM   holidays
 24           WHERE  holiday NOT BETWEEN To_date('01/01/2015', 'DD/MM/YYYY') AND
 25                                      To_date('31/03/2015', 'DD/MM/YYYY'))
 26  SELECT weekday_count - holiday_count as working_day_count
 27  FROM   count_of_weekdays,
 28         count_of_holidays
 29  /

WORKING_DAY_COUNT
-----------------
              258

SQL>

There were total 261 weekdays, out of which there were 3 holidays in holiday table. So, total count of working days in the output is 261 - 3 = 258.

回答2:

SELECT To_date(sysdate)- To_date('01.07.2014','DD.MM.YYYY')
- (2 * (to_char(To_date(sysdate), 'WW') - to_char(To_date('01.07.2014','DD.MM.YYYY'), 'WW'))) AS DAYS_BETWEEN 
FROM dual