在 Oracle SQL 中计算工作日（无函数或过程）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14898357/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate business days in Oracle SQL(no functions or procedure)
提问by yochim
I am trying to calculate business days between two dates in Oracle select. I got to the point when my calculation gives most results correct for given dates (I compare it with NETWORKDAYS in excel) but sometimes it varies from 2 days to -2 days - and I don't know why...
我正在尝试计算 Oracle select 中两个日期之间的工作日。当我的计算给出大多数结果对于给定日期是正确的（我将它与 Excel 中的 NETWORKDAYS 进行比较）时，我已经到了这一点，但有时它从 2 天到 -2 天不等——我不知道为什么......
Here's my code:
这是我的代码：
SELECT
((to_char(CompleteDate,'J') - to_char(InstallDate,'J'))+1) - (((to_char(CompleteDate,'WW')+ (52 * ((to_char(CompleteDate,'YYYY') - to_char(InstallDate,'YYYY'))))) - to_char(InstallDate,'WW'))*2) as BusinessDays
FROM TABLE
Thanks!
谢谢！
回答by yochim
The solution, finally:
解决方案，最后：
SELECT OrderNumber, InstallDate, CompleteDate,
(TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 -
((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDays
FROM Orders
ORDER BY OrderNumber;
Thanks for all your responses !
感谢您的所有回复！
回答by OraGeek
I took into account all the different approaches discussed above and came up with a simple query that gives us the number of working days in each month of the year between two dates:
我考虑了上面讨论的所有不同方法，并提出了一个简单的查询，它为我们提供了两个日期之间一年中每个月的工作日数：
WITH test_data AS
(
SELECT TO_DATE('01-JAN-14') AS start_date,
TO_DATE('31-DEC-14') AS end_date
FROM dual
),
all_dates AS
(
SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day
FROM test_data td
CONNECT BY td.start_date + LEVEL-1 <= td.end_date)
SELECT TO_CHAR(week_day, 'MON'), COUNT(*)
FROM all_dates
WHERE to_char(week_day, 'dy', 'nls_date_language=AMERICAN') NOT IN ('sun' , 'sat')
GROUP BY TO_CHAR(week_day, 'MON');
WITH test_data AS
(
SELECT TO_DATE('01-JAN-14') AS start_date,
TO_DATE('31-DEC-14') AS end_date
FROM dual
),
all_dates AS
(
SELECT td.start_date, td.end_date, td.start_date + LEVEL-1 as week_day
FROM test_data td
CONNECT BY td.start_date + LEVEL-1 <= td.end_date)
SELECT TO_CHAR(week_day, 'MON'), COUNT(*)
FROM all_dates
WHERE to_char(week_day, 'dy', 'nls_date_language=AMERICAN') NOT IN ('sun' , 'sat')
GROUP BY TO_CHAR(week_day, 'MON');
Please feel free to modify the query as needed.
请随时根据需要修改查询。
回答by Art
I changed my example to more readable and to return count of bus. days between. I do not know why you need 'J'- Julian format. All it takes is start/Install and end/Complete dates. You will get correct number of days between 2 dates using this. Replace my dates with yours, add NLS if needed...:
我将示例更改为更具可读性并返回总线计数。天之间。我不知道你为什么需要'J'-Julian 格式。所需要的只是开始/安装和结束/完成日期。使用此方法，您将获得 2 个日期之间的正确天数。用你的日期替换我的日期，如果需要添加 NLS...：
SELECT Count(*) BusDaysBtwn
FROM
(
SELECT TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1 InstallDate -- MON or any other day
, TO_DATE('2013-02-25', 'YYYY-MM-DD') CompleteDate -- MON or any other day
, TO_CHAR(TO_DATE('2013-02-18', 'YYYY-MM-DD') + LEVEL-1, 'DY') InstallDay -- day of week
FROM dual
CONNECT BY LEVEL <= (TO_DATE('2013-02-25', 'YYYY-MM-DD') - TO_DATE('2013-02-18', 'YYYY-MM-DD')) -- end_date - start_date
)
WHERE InstallDay NOT IN ('SAT', 'SUN')
/
SQL> 5
回答by A.B.Cade
Try this:
尝试这个：
with holidays as
(
select d from (
select minDate + level -1 d
from (select min(submitDate) minDate, max (completeDate) maxDate
from t)
connect by level <= maxDate - mindate + 1)
where to_char(d, 'dy', 'nls_date_language=AMERICAN') not in ('sun' , 'sat')
)
select t.OrderNo, t.submitDate, t.completeDate, count(*) businessDays
from t join holidays h on h.d between t.submitDate and t.completeDate
group by t.OrderNo, t.submitDate, t.completeDate
order by orderno
回答by Hiran
I see that marked final solution is not correct always. Suppose, InstallDate is 1st of the month (if falls on Saturday) and CompleteDate is 16th of the month (if falls on Sunday)
我看到标记的最终解决方案并不总是正确的。假设 InstallDate 是本月的 1 号（如果是星期六），而 CompleteDate 是本月的 16 号（如果是星期日）
In that case, actual Business Days is 10 but the marked query result will give the answer as 12. So, we have to treat this type of cases too, which I used
在这种情况下，实际工作日为 10，但标记的查询结果将给出答案为 12。因此，我们也必须处理这种类型的情况，我使用了
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END
line to handle it.
线来处理它。
SELECT OrderNumber, InstallDate, CompleteDate,
(TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 -
((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SAT' AND TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SUN' THEN 2 ELSE 0 END)as BusinessDays
FROM Orders
ORDER BY OrderNumber;
回答by Serpiton
To just remove sundays and saturdays you can use this
要删除星期日和星期六，您可以使用它
SELECT Base_DateDiff
- (floor((Base_DateDiff + 0 + Start_WeekDay) / 7))
- (floor((Base_DateDiff + 1 + Start_WeekDay) / 7))
FROM (SELECT 1 + TRUNC(InstallDate) - TRUNC(InstallDate, 'IW') Start_WeekDay
, CompleteDate - InstallDate + 1 Base_DateDiff
FROM TABLE) a
Base_DateDiffcounts the number of days between the two dates(floor((Base_DateDiff + 0 + Start_WeekDay) / 7))counts the number of sundays(floor((Base_DateDiff + 1 + Start_WeekDay) / 7))counts the number of saturdays
Base_DateDiff计算两个日期之间的天(floor((Base_DateDiff + 0 + Start_WeekDay) / 7))数 计算星期日(floor((Base_DateDiff + 1 + Start_WeekDay) / 7))的数目 计算星期六的数目
1 + TRUNC(InstallDate) - TRUNC(InstallDate, 'IW')get 1 for mondays to 7 for sunday
1 + TRUNC(InstallDate) - TRUNC(InstallDate, 'IW')周一获得 1 至周日获得 7
回答by beckham12a18
This query can be used to go backward N days from the given date (business days only)
此查询可用于从给定日期后退 N 天（仅限工作日）
For example, go backward 15 days from 2017-05-17:
例如，从 2017-05-17 倒退 15 天：
select date_point, closest_saturday - (15 - offset + floor((15 - offset) / 6) * 2) from(
select date_point,
closest_saturday,
(case
when weekday_num > 1 then
weekday_num - 2
else
0
end) offset
from (
select to_date('2017-05-17', 'yyyy-mm-dd') date_point,
to_date('2017-05-17', 'yyyy-mm-dd') - to_char(to_date('2017-05-17', 'yyyy-mm-dd'), 'D') closest_saturday,
to_char(to_date('2017-05-17', 'yyyy-mm-dd'), 'D') weekday_num
from dual
))
Some brief explanation: suppose we want to go backward N days from a given date - Find the closest Saturday that is less than or equal to the given date. - From the closest Saturday, go back ward (N - offset) days. offset is the number of business days between the closest Saturday and the given date (excluding the given date).
一些简短的解释：假设我们想从给定日期倒退 N 天 - 找到小于或等于给定日期的最近星期六。- 从最近的星期六开始，返回病房 (N - offset) 天。offset 是最近的星期六和给定日期（不包括给定日期）之间的工作日数。
*To go back M days from a Saturday (business days only), use this formula DateOfMonthOfTheSaturday - [M + Floor(M / 6) * 2]
*要从星期六返回 M 天（仅限工作日），请使用此公式 DateOfMonthOfTheSaturday - [M + Floor(M / 6) * 2]
回答by deanashuff
The accepted solution is quite close but seems wrong in some cases (e.g., 2/1/2015 through 2-28/2015 or 5/1/2015 through 5/31/2015). Here's a refined version...
接受的解决方案非常接近，但在某些情况下似乎是错误的（例如，2/1/2015 到 2-28/2015 或 5/1/2015 到 5/31/2015）。这是一个精致的版本......
end_date-begin_date+1 /* total days */
- TRUNC(2*(end_date-begin_date+1)/7) /* weekend days in whole weeks */
- (CASE
WHEN TO_CHAR(begin_date,'D') = 1 AND REMAINDER(end_date-begin_date+1,7) > 0 THEN 1
WHEN TO_CHAR(begin_date,'D') = 8 - REMAINDER(end_date-begin_date+1,7) THEN 1
WHEN TO_CHAR(begin_date,'D') > 8 - REMAINDER(end_date-begin_date+1,7) THEN 2
ELSE 0
END) /* weekend days in partial week */
AS business_days
The part that handles the multiples of 7 (whole weeks) is good. But, when considering the partial week portion, it depends on both the day-of-week offset and the number of days in the partial portion, according to the following matrix...
处理 7 的倍数（整周）的部分很好。但是，在考虑部分周部分时，它取决于星期几偏移量和部分部分中的天数，根据以下矩阵...
654321
1N 111111
2M 100000
3T 210000
4W 221000
5R 222100
6F 222210
7S 222221
回答by Stephen
Here is a function that is fast and flexible. You can count any weekday in a date range.
这是一个快速灵活的功能。您可以计算日期范围内的任何工作日。
CREATE OR REPLACE FUNCTION wfportal.cx_count_specific_weekdays( p_week_days VARCHAR2 DEFAULT 'MON,TUE,WED,THU,FRI'
, p_start_date DATE
, p_end_date DATE)
RETURN NUMBER
IS
/***************************************************************************************************************
*
* FUNCTION DESCRIPTION:
*
* This function calculates the total required week days in a date range.
*
* PARAMETERS:
*
* p_week_days VARCHAR2 The week days that need to be counted, comma seperated e.g. MON,TUE,WED,THU,FRU,SAT,SUN
* p_start_date DATE The start date
* p_end_date DATE The end date
*
* CHANGE history
*
* No. Date Changed by Change Description
* ---- ----------- ------------- -------------------------------------------------------------------------
* 0 07-May-2013 yourname Created
*
***************************************************************************************************************/
v_date_end_first_date_range DATE;
v_date_start_last_date_range DATE;
v_total_days_in_the_weeks NUMBER;
v_total_days_first_date_range NUMBER;
v_total_days_last_date_range NUMBER;
v_output NUMBER;
v_error_text CX_ERROR_CODES.ERROR_MESSAGE%TYPE;
--Count the required days in a specific date ranges by using a list of all the weekdays in that range.
CURSOR c_total_days ( v_start_date DATE
, v_end_date DATE ) IS
SELECT COUNT(*) total_days
FROM ( SELECT ( v_start_date + level - 1) days
FROM dual
CONNECT BY LEVEL <= ( v_end_date - v_start_date ) + 1
)
WHERE INSTR( ',' || p_week_days || ',', ',' || TO_CHAR( days, 'DY', 'NLS_DATE_LANGUAGE=english') || ',', 1 ) > 0
;
--Calculate the first and last date range by retrieving the first Sunday after the start date and the last Monday before the end date.
--Calculate the total amount of weeks in between and multiply that with the total required days.
CURSOR c_calculate_new_dates ( v_start_date DATE
, v_end_date DATE ) IS
SELECT date_end_first_date_range
, date_start_last_date_range
, (
(
( date_start_last_date_range - ( date_end_first_date_range + 1 ) )
) / 7
) * total_required_days total_days_in_the_weeks --The total amount of required days
FROM ( SELECT v_start_date + DECODE( TO_CHAR( v_start_date, 'DY', 'NLS_DATE_LANGUAGE=english')
, 'MON', 6
, 'TUE', 5
, 'WED', 4
, 'THU', 3
, 'FRI', 2
, 'SAT', 1
, 'SUN', 0
, 0 ) date_end_first_date_range
, v_end_date - DECODE( TO_CHAR( v_end_date, 'DY', 'NLS_DATE_LANGUAGE=english')
, 'MON', 0
, 'TUE', 1
, 'WED', 2
, 'THU', 3
, 'FRI', 4
, 'SAT', 5
, 'SUN', 6
, 0 ) date_start_last_date_range
, REGEXP_COUNT( p_week_days, ',' ) + 1 total_required_days --Count the commas + 1 to get the total required weekdays
FROM dual
)
;
BEGIN
--Verify that the start date is before the end date
IF p_start_date < p_end_date THEN
--Get the new calculated days.
OPEN c_calculate_new_dates( p_start_date, p_end_date );
FETCH c_calculate_new_dates INTO v_date_end_first_date_range
, v_date_start_last_date_range
, v_total_days_in_the_weeks;
CLOSE c_calculate_new_dates;
--Calculate the days in the first date range
OPEN c_total_days( p_start_date, v_date_end_first_date_range );
FETCH c_total_days INTO v_total_days_first_date_range;
CLOSE c_total_days;
--Calculate the days in the last date range
OPEN c_total_days( v_date_start_last_date_range, p_end_date );
FETCH c_total_days INTO v_total_days_last_date_range;
CLOSE c_total_days;
--Sum the total required days
v_output := v_total_days_first_date_range + v_total_days_last_date_range + v_total_days_in_the_weeks;
ELSE
v_output := 0;
END IF;
RETURN v_output;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END cx_count_specific_weekdays;
/
回答by MChristiaan
Here you go...
干得好...
- First check how many days you got in the holiday table, excluding weekend days.
Get business days (MON to FRI) between the 2 dates and after that subtract the holiday days.
create or replace FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE) RETURN NUMBER IS v_holidays NUMBER; v_start_date DATE := TRUNC (p_start_date); v_end_date DATE := TRUNC (p_end_date); BEGIN IF v_end_date >= v_start_date THEN SELECT COUNT (*) INTO v_holidays FROM holidays WHERE day BETWEEN v_start_date AND v_end_date AND day NOT IN ( SELECT hol.day FROM holidays hol WHERE MOD(TO_CHAR(hol.day, 'J'), 7) + 1 IN (6, 7) ); RETURN GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0) + ( ( NEXT_DAY (v_end_date, 'MON') - NEXT_DAY (v_start_date, 'MON') ) / 7 ) * 5 - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0) - v_holidays; ELSE RETURN NULL; END IF; END calculate_business_days;
- 首先检查您在假期表中的天数，不包括周末天数。
获取两个日期之间的工作日（周一到周五），然后减去假期天数。
create or replace FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE) RETURN NUMBER IS v_holidays NUMBER; v_start_date DATE := TRUNC (p_start_date); v_end_date DATE := TRUNC (p_end_date); BEGIN IF v_end_date >= v_start_date THEN SELECT COUNT (*) INTO v_holidays FROM holidays WHERE day BETWEEN v_start_date AND v_end_date AND day NOT IN ( SELECT hol.day FROM holidays hol WHERE MOD(TO_CHAR(hol.day, 'J'), 7) + 1 IN (6, 7) ); RETURN GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0) + ( ( NEXT_DAY (v_end_date, 'MON') - NEXT_DAY (v_start_date, 'MON') ) / 7 ) * 5 - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0) - v_holidays; ELSE RETURN NULL; END IF; END calculate_business_days;
After that you can test it out, like:
之后，您可以对其进行测试，例如：
select
calculate_business_days('21-AUG-2013','28-AUG-2013') as business_days
from dual;
