Oracle查询以获取两个日期之间的工作日数,不包括节假日 [英] Oracle query to get the number of business days between 2 dates, excluding holidays
问题描述
我们正在使用Oracle 11.
We are using Oracle 11.
在我们的CASE WHEN语句中,我需要检查两个日期之间的天数是否大于3个工作日(因此不包括周末和节假日).
In our CASE WHEN statement, I need to check if the number of days between the 2 dates are > 3 business days (so excluding weekends and holidays).
CASE WHEN end_date - start_date > 3 THEN 0 --> this includes weekend
and holidays
WHEN CODE = 1 THEN 1
WHEN CODE =2 THEN 2
ELSE 3
END AS MyColumn
说我有一个假期日历表,该表的HolidayDates列包含所有假期,例如:12/25/2018、12/31/2018等.
Say I have a holiday calendar table that has column HolidayDates that contains all the holidays, for ex: 12/25/2018, 12/31/2018, etc.
HolidayDates
12/25/2018
12/31/2018
所以,如果
日期1 = 1/2/19(星期三)
Date1 = 1/2/19 (Wednesday)
Date2 = 18/27/18(星期四)
Date2 = 12/27/18 (Thursday)
日期1和日期2之间的工作日数为3天(12/27、12/28和12/31).
The number of business days in between Date1 and Date2 is 3 days (12/27, 12/28 and 12/31).
以下查询将获得不包括周末的工作日数.
The below query will get the number of business days excluding weekends.
如何在此查询中也排除假期?
How do I also exclude holidays in this query ?
SELECT TO_CHAR( start_date, 'YYYY-MM-DD "("DY")"') AS start_date,
( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7
+ LEAST( TRUNC( end_date ) - TRUNC( end_date, 'IW' ) + 1, 5 )
- LEAST( TRUNC( start_date ) - TRUNC( start_date, 'IW' ), 5 )
AS Num_Week_Days
FROM table_name;
谢谢.
推荐答案
Taking the code in this previous answer and converting it from a function to a query gives:
Oracle设置:
CREATE TABLE Holidays ( HolidayDates ) AS
SELECT DATE '2018-12-25' FROM DUAL UNION ALL
SELECT DATE '2018-12-31' FROM DUAL;
CREATE TABLE table_name ( start_date, end_date ) AS
SELECT DATE '2018-12-21', DATE '2018-12-26' FROM DUAL UNION ALL
SELECT DATE '2018-12-28', DATE '2019-01-01' FROM DUAL;
查询:
SELECT t.*,
( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * 5 / 7
+ LEAST( TRUNC( end_date ) - TRUNC( end_date, 'IW' ) + 1, 5 )
- LEAST( TRUNC( start_date ) - TRUNC( start_date, 'IW' ), 5 )
- ( SELECT COUNT(1)
FROM holidays
WHERE HolidayDates BETWEEN t.start_date AND t.end_date
-- Exclude any weekend holidays so we don't double count.
AND TRUNC( HolidayDates ) - TRUNC( HolidayDates, 'IW' ) <= 5
)
AS Num_Week_Days
FROM table_name t;
输出:
START_DATE | END_DATE | NUM_WEEK_DAYS
:--------- | :-------- | ------------:
21-DEC-18 | 26-DEC-18 | 3
28-DEC-18 | 01-JAN-19 | 2
01-JAN-19 | 07-JAN-19 | 5
db<>小提琴此处
这篇关于Oracle查询以获取两个日期之间的工作日数,不包括节假日的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!