如何在oracle中找到不包括星期六，星期日和节假日的48个工作小时日期 [英] How to find 48 working hours date excluding saturday,sundays and holidays in oracle
问题描述
要求找到某个模块的第 48 和第 24 工作小时.
There is a requirement to find the 48th and 24th working hour for some module.
要求:
假设如果我将 2nd May 作为参数传递给函数，输出应该是 27th April for 48 hours 和 4 月 28 日24 小时(因为 5 月 1 日是假期，4 月 29 日和 30 日属于周六和周日)
For suppose if I pass 2nd May as a parameter to the function, the output should be 27th April for 48 hours and 28th APRIL for 24 hours (as 1st May is holiday and 29th and 30th April fall under Saturday and Sunday)
问题在于连续两个假期.例如，要创建 dummy 数据，我们插入 2nd May 作为假期并在 3rd May 运行代码，该代码应该检索 27th April 的 48 hours 和 28th APRIL 为 24 hours.
The problem is with two successive holidays. For example, to create a dummy data we insert 2nd May as a holiday and run the code on 3rd May that should retrieve 27th April for 48 hours and 28th APRIL for 24 hours.
但我的功能似乎在连续假期都不起作用.某处计数器增量似乎位于错误的位置.
But my function doesn't seem to be working for successive holidays. Somewhere the counter increment seems to be in the wrong location.
注意事项:周末:周六和周日需要排除的天数:给定假日日历中的周六、周日和节假日:
Considerations: Weekend : Saturday and Sunday Days that need to be excluded: Saturdays, Sundays and Holidays in the given holiday calendar:
假日表创建:
CREATE TABLE HOLIDAY_TAB
(
HOL_DATE DATE,
DESCRIPTION VARCHAR2 (100) DEFAULT NULL
);
insert into HOLIDAY_TAB values (TO_DATE ('26-Jan-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('29-Mar-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('14-Apr-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('01-May-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('02-Jun-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('26-Jun-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('15-Aug-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('25-Aug-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('28-Sep-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('02-Oct-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('19-Oct-2017', 'DD-MON-YYYY'),NULL);
insert into HOLIDAY_TAB values (TO_DATE ('25-Dec-2017', 'DD-MON-YYYY'),NULL);
commit;
为捕捉假期而编写的函数:
Function to written to capture holiday :
功能:
CREATE OR REPLACE FUNCTION CSE.F_HOL_CHECK_ABC (i_hol_date DATE)
RETURN DATE
AS
valid_working_day DATE := i_hol_date;
day_C holiday_nvs%ROWTYPE;
CURSOR c_hol
IS
SELECT *
FROM HOLIDAY_TAB
WHERE TRUNC (hol_date) = TRUNC (i_hol_date);
CURSOR c_hol_24
IS
SELECT *
FROM HOLIDAY_TAB
WHERE TRUNC (hol_date) = TRUNC (i_hol_date + 3);
CURSOR c_hol_48
IS
SELECT *
FROM HOLIDAY_NVS
WHERE TRUNC (hol_date) = TRUNC (i_hol_date + 2);
BEGIN
-- FOR rec24 IN c_hol_24
-- LOOP
-- IF (rec24.hol_date IS NOT NULL)
-- THEN
-- valid_working_day := i_hol_date - 1;
-- END IF;
-- END LOOP;
OPEN c_hol;
FETCH c_hol INTO day_C;
IF c_hol%FOUND
THEN
SELECT DECODE (TO_CHAR (i_hol_date - 1, 'D'),
1, i_hol_date - 3,
i_hol_date - )
INTO valid_working_day
FROM DUAL;
END IF;
CLOSE c_hol;
RETURN (valid_working_day);
END;
/
不确定函数是否正确.但是有一种奇怪的情况，与日期文字相比，尝试使用 SYSDATE 时我的查询没有给出相同的结果.
Not sure if the function is right. But there is a weird situation that my query doesn't give same result when tried using the SYSDATE compared to date literal.
手动运行带日期的 1:
SELECT TRUNC (
DECODE (
TO_CHAR (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY'), 'D'),
2, F_HOL_CHECK_ABC (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY') - 4),
DECODE (
TO_CHAR (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY'), 'D'),
3, F_HOL_CHECK_ABC (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY') - 4),
F_HOL_CHECK_ABC (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY') - 2))))
AS "48HOURS",
TRUNC (
DECODE (
TO_CHAR (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY'), 'D'),
2, F_HOL_CHECK_ABC (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY') - 3),
F_HOL_CHECK_ABC (TO_DATE ('03-MAY-2017', 'DD-MON-YYYY') - 1)))
AS "24HOURS"
FROM DUAL;
使用 sysdate 运行 2:
SELECT TRUNC (
DECODE (
TO_CHAR (SYSDATE, 'D'),
2, F_HOL_CHECK_ABC (SYSDATE - 4),
DECODE (TO_CHAR (SYSDATE, 'D'),
3, F_HOL_CHECK_ABC (SYSDATE - 4),
F_HOL_CHECK_ABC (SYSDATE -2)))) as "48 hour" ,
TRUNC (
DECODE (
TO_CHAR (SYSDATE,
'D'),
2, F_HOL_CHECK_ABC (
SYSDATE - 3),
F_HOL_CHECK_ABC (
SYSDATE -1))) as "24 hour" from dual;
非常感谢这方面的任何帮助.我所需要的只是跳过假期，周日和周六，即；非工作时间，给我一天 48 小时工作时间和一天 24 小时工作时间
Any help in this is really appreciated. All I need is to skip the holidays, Sundays and Saturdays i.e; non working hours and give me 48 working hour day and 24 hour working hour day
这是使用计数器的代码 2 的另一个尝试:
Here is another attempt with code 2 using counters:
CREATE OR REPLACE FUNCTION CSE.F_HOL_CHECK_S_NS (i_hol_date DATE,
i_S_NS NUMBER)
RETURN DATE
AS
valid_working_day DATE := i_hol_date;
counter NUMBER := 0;
day_number NUMBER := 0;
hol_count NUMBER := 0;
day_C holiday_nvs%ROWTYPE;
CURSOR c_hol (hol_date_c DATE)
IS
SELECT *
FROM HOLIDAY_TAB
WHERE TRUNC (hol_date) = TRUNC (TO_DATE (hol_date_c, 'DD-MON-YYYY'));
BEGIN
IF i_S_NS = 0
THEN
LOOP
IF c_hol%ISOPEN
THEN
CLOSE c_hol;
END IF;
OPEN c_hol (valid_working_day);
IF c_hol%FOUND
THEN
valid_working_day := valid_working_day - 1;
SELECT TO_CHAR (TO_DATE (valid_working_day, 'DD-MON-YYYY'), 'D')
INTO day_number
FROM DUAL;
SELECT COUNT (*)
INTO hol_count
FROM HOLIDAY_TAB
WHERE TRUNC (hol_date) =
TRUNC (TO_DATE (valid_working_day, 'DD-MON-YYYY'));
--valid_working_day:=valid_working_day-1;
-- SELECT DECODE (TO_CHAR (valid_working_day - 1, 'D'),
-- 1, valid_working_day - 3,
-- valid_working_day - 1)
-- INTO valid_working_day
-- FROM DUAL;
IF (hol_count > 0)
THEN
valid_working_day := valid_working_day - 1;
-- counter := counter + 1;
ELSIF (day_number = 1 OR day_number = 7)
THEN
valid_working_day := valid_working_day - 1;
END IF;
ELSIF ( TO_CHAR (TO_DATE (valid_working_day, 'DD-MON-YYYY'), 'D') =
1
OR TO_CHAR (TO_DATE (valid_working_day, 'DD-MON-YYYY'), 'D') =
7)
THEN
valid_working_day := valid_working_day - 1;
ELSE
counter := counter + 1;
valid_working_day := valid_working_day - 1;
END IF;
EXIT WHEN counter >= 3;
END LOOP;
--elsif (i_S_NS <> 0) then
--null;
END IF;
--valid_working_day := valid_working_day - 1;
RETURN (valid_working_day);
END;
推荐答案
这是一个纯 SQL 答案.诀窍是生成一系列涵盖所有可能性的先前日期.在我所知道的国家/地区，连续的公共假期不超过两个(英国的圣诞节和复活节，苏格兰的 Hogmanay).也允许周末，这意味着最多可以排除四天.
Here is a pure SQL answer. The trick is to generate a series of prior dates which covers all eventualities. In countries that I'm aware of, there are no more than two public holidays in a row (Christmas and Easter in the UK, Hogmanay in Scotland). Allowing for the weekend too that means there can be up to four days to be excluded from consideration.
正如评论者指出的那样，在其他国家/地区可能会有更长的公共假期，因此您可能需要相应地调整偏移量.
As commenters have pointed out, in other countries there may longer runs of public holidays, so you may need to adjust the offset accordingly.
无论如何，有两天的目标，我们需要一个回溯六天的范围(再加上一个运气).这将为我们提供目标日期前 7 天的结果集以及:
Anyway, with two days to target we need a range going back six days (plus one for luck). This will gives us a result set of the seven days preceding the target date and the :
select (tgt_date - 7) + (level-1)
from dual
connect by level <= 7
现在我们准备好了.我们可以使用带有 'IW' 日期掩码的技巧来确定星期几作为数字，并以文化中立的方式排除星期六和星期日.我们可以离开加入 holiday_tab 以排除公共假期.然后我们对剩下的进行排名并选择最近的两个日期:
Now we're set. We can use a trick with the 'IW' date mask to determine the day of the week as a number and rule out Saturdays and Sundays in a culturally neutral fashion. We can left join to holiday_tab to rule out public holidays. Then we rank what's left and select the most recent two dates:
SQL> with hdr as (
2 select dr.dt
3 , case
4 when (1 + dt - trunc(dr.dt, 'IW') in (6,7) then 1
5 when h.hol_date is not null then 1
6 else 0
7 end as hol
8 from ( select trunc(date '2017-05-02' - 7) + (level-1) as dt
9 from dual
10 connect by level <= 7
11 ) dr
12 left join holiday_tab h
13 on h.hol_date = dr.dt
14 )
15 , rhdr as (
16 select hdr.dt
17 , row_number() over (order by hdr.dt desc) rn
18 from hdr
19 where hdr.hol = 0
20 )
21 select rhdr.dt
22 , decode( rhdr.rn, 1, '24hr', '48hr') as cat
23 , to_char(rhdr.dt, 'DY') as dy
24 from rhdr
25 where rn <= 2;
DT CAT DY
--------- ---- ------------
28-APR-17 24hr FRI
27-APR-17 48hr THU
SQL>
鉴于昨天 02-MAY-2017 作为目标日期，这将跳过星期一(五一假期)和周末以确定前两个工作日.
Given yesterday 02-MAY-2017 as the target date this skips Monday (Mayday holiday) and the weekend to identify the two previous working days.
如果你需要一个函数，你可以这样做:
If you need a function you could do something like this:
create or replace type dt_nt as table of date;
create or replace function prior_working_days
( p_target_date in date
, p_no_of_days in number := 2)
return dt_nt
is
return_value dt_nt;
offset pls_integer := (p_no_of_days+4+1);
begin
with hdr as (
select dr.dt
, case
when to_char(1 + dt - trunc(dr.dt, 'IW') in (6,7) then 1
when h.hol_date is not null then 1
else 0
end as hol
from ( select (trunc(p_target_date) - offset) + (level-1) as dt
from dual
connect by level <= offset
) dr
left join holiday_tab h
on h.hol_date = dr.dt
)
, rhdr as (
select hdr.dt
, row_number() over (order by hdr.dt desc) rn
from hdr
where hdr.hol = 0
)
select rhdr.dt
bulk collect into return_value
from rhdr
where rn <= p_no_of_days;
return return_value;
end prior_working_days;
/
这将返回一个 SQL 日期表:
This returns a SQL table of dates:
SQL> select * from table( prior_working_days(sysdate));
COLUMN_VA
---------
02-MAY-17
28-APR-17
SQL>
这篇关于如何在oracle中找到不包括星期六，星期日和节假日的48个工作小时日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
