函式 number_of_days(start_date, end_date) 應該計算 start_date 和 end_date 之間有多少作業日。只要不是sunday ，它就適用于任何 start_date 。
計算有3個步驟：
取整周前的天數（對于 21 至 24 日等小范圍日期，整周可以為 0）
然后它增加了整周
將整個星期后剩余的所有天數加起來。
我需要第二步來生成像 0、5、10 這樣的整數，而不是在這個例子中得到 0,7142857142857142857142857142857142857，因為 start_date 引數是星期日。
我可以使用 ROUND() 來解決它，但也許有更好的方法？
CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
RETURN NUMBER
IS v_number_of_days NUMBER;
first_week_day DATE := TO_DATE('31-12-2017', 'DD-MM-YYYY');
BEGIN
--step 1
SELECT ( CASE WHEN MOD(start_date - first_week_day, 7) BETWEEN 2 AND 5
THEN 6 - MOD(start_date - first_week_day, 7)
ELSE 0 END )
--step 2
(( CASE WHEN MOD(end_date - first_week_day, 7) < 7
THEN end_date - MOD(end_date - first_week_day, 7)
ELSE end_date END )
-
( CASE WHEN MOD(start_date - first_week_day, 7) > 1
THEN start_date 8 - MOD(start_date - first_week_day, 7)
ELSE start_date END ) 1
) / 7 * 5
--step 3
( CASE WHEN MOD(end_date - first_week_day,7) BETWEEN 1 AND 6
THEN CASE WHEN MOD(end_date - first_week_day, 7) = 6
THEN MOD(end_date - first_week_day, 7) - 1
ELSE MOD(end_date - first_week_day, 7) END
ELSE 0 END )
INTO v_number_of_days
FROM DUAL;
RETURN v_number_of_days;
END;
--test
SELECT number_of_days(TO_DATE('21-11-2021', 'DD-MM-YYYY'), TO_DATE('24-11-2021', 'DD-MM-YYYY'))
FROM DUAL;
uj5u.com熱心網友回復：
這應該讓你開始，因為我不明白你的要求。
with time_between as (
select trunc ( timestamp'2021-06-14 06:00:00' ) -
trunc ( timestamp'2021-06-08 14:00:00' ) t
from dual
)
select *
from time_between;
T
6
uj5u.com熱心網友回復：
你只是讓這太復雜了......首先，最好在 ISO 周內作業：它從星期一開始。
- https://en.wikipedia.org/wiki/ISO_week_date
- https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm
- https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/TRUNC-date.html#GUID-BC82227A-2698-4EC8-8C1A-ABECC64B0E79
因此，您可以使用trunc(dt, 'IW'). 例如，11 月 19 日是星期五，我們可以使用以下命令輕松獲取本周的第一天trunc(date'2021-11-19','IW')：
SQL> select trunc(date'2021-11-19','IW') xx from dual;
XX
-------------------
2021-11-15 00:00:00
所以 2021-11-15 是星期一。
現在讓我們假設我們有一些日期：
Mo Tu We Th Fr Sa Su
-- -- -- -- -- -- --
X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X X X X X X
X X
我們可以很容易地得到整周的作業日數 (count_full_weeks * 5/7)，所以讓我們排除它們：
Mo Tu We Th Fr Sa Su
-- -- -- -- -- -- --
X X X X
X X
然后，讓我們以圖形方式對其進行轉換，因為我們知道剩余天數將始終少于 7 天（少于一周）并且第一天將是前 7 天之一：
Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su
-- -- -- -- -- -- -- -- -- -- -- -- -- --
X X X X X X
讓我們用他們的數字替換 Mo,Tu,We...：
0 1 2 3 4 5 6 0 1 2 3 4 5 6
-- -- -- -- -- -- -- -- -- -- -- -- -- --
X X X X X X
由于我們知道這部分周中的天數不能為 7，因此我們知道最晚的結束時間為：
0 1 2 3 4 5 6 0 1 2 3 4 5 6
-- -- -- -- -- -- -- -- -- -- -- -- -- --
X X X X X X
現在讓我們將第二周的數字替換為 7-13：
0 1 2 3 4 5 6 7 8 9 10 11 12 13
-- -- -- -- -- -- -- -- -- -- -- -- -- --
X X X X X X
現在我們可以理解我們只需要檢查第 5 天和第 6 天，即檢查我們的經期是否包含 5 和 6。
所以它會是這樣的（我已經盡可能詳細了）：
CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
RETURN NUMBER
as
days_between int;
full_weeks int;
remaining_days int;
remaining_workdays int;
v_first_week_day date;
v_start int;
v_end int;
result int;
begin
days_between := end_date - start_date;
full_weeks := trunc(days_between/7);
remaining_days:= mod(days_between,7); -- or days_between-full_weeks*7
v_first_week_day := trunc(start_date,'IW');
v_start := start_date - v_first_week_day;
v_end := v_start remaining_days - 1;
remaining_workdays := remaining_days
- case when 5 between v_start and v_end then 1 else 0 end
- case when 6 between v_start and v_end then 1 else 0 end
;
result := full_weeks * 5 remaining_workdays;
RETURN result;
end;
/
我做了很多子步驟和中間計算和變數，只是為了讓它更清楚。顯然，你可以讓它更短，例如：
CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
RETURN NUMBER
as
begin
return 5*trunc((end_date-start_date)/7) mod((end_date-start_date),7)
- case when 5-(start_date - trunc(start_date,'IW')) between 0 and mod((end_date-start_date),7)-1 then 1 else 0 end
- case when 6-(start_date - trunc(start_date,'IW')) between 0 and mod((end_date-start_date),7)-1 then 1 else 0 end
;
end;
/
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/362588.html
上一篇：我如何多次內部加入和分組？