sql oracle忽略假期 [英] 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
我有另一个表table1，其中存在date列（其类型为DATE），其中假期的所有日期是写下来。
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.
示例表1：
DATES
12.06.2011
19.06.2014
09.05.2013
...
我试图让我的代码检查这个表，如果一个日期是在上述两个日期之间，则在输出中使-1天。
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.
推荐答案
如果将它分成以下任务，应该很容易：
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.
以下查询将计算2014年1月1日 与 2014年12月31日之间的工作天数，不包括假期如表中所述。
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.
WITH 子句仅用作表格，您可以使用假日表。
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>
总共有 261个工作日，其中 假日表格中的3个假日。所以，输出中的总工作日数是 261 - 3 = 258 。
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.
这篇关于sql oracle忽略假期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
