当月最后一个工作日的Oracle SQL，包括甲骨文中的联邦假日

Question

我正在尝试查找Oracle SQL以查找本月的最后一个工作日以及上个月的最后一个工作日。这两种情况都应该考虑联邦假日日历。

例如：

• 当月最后一个工作日
• 如果我在2019年11月15日运行

从技术上讲，我应该在 11 月 29 日获得我的输出，因为 28 日是感恩节假期。

Answer 1

将这些联邦假日作为DATE类型存储在假日表中，然后 尝试这样的事情：查找该月最后七天中最早的(MAX(天，既不是星期六也不是星期日，也不是中提到的假日 假日表。

这里的假设是：1(月底的七天不能都是假期或周末，2(周六和周日休息。您可以相应地调整level或 where 子句，具体取决于上述假设是否应始终成立。

SELECT MAX(dt) AS last_working_day 
FROM
(
SELECT last_day(SYSDATE) - level + 1 as dt
FROM dual CONNECT BY
level <= 7  -- the last seven days of the month
)  WHERE TO_CHAR(dt,'DY', 'NLS_DATE_LANGUAGE = AMERICAN') NOT IN ('SAT','SUN')
AND dt NOT IN ( SELECT holiday from federal_holidays );

更好的方法是有一个日历表，其中包含一年中的所有日期和名为isbusinessday的预定义列。然后查询会简单得多。

SELECT MAX(dt)
FROM calendar
WHERE isbusinessday = 'Y' 
AND TO_CHAR(dt,'YYYYMM') = TO_CHAR(SYSDATE,'YYYYMM');

Answer 2

拥有假日表通常是有效的，但是由于某些假期正在移动，因此需要一些维护。例如，感恩节是 11 月的第 4 个星期四，即从 11 月 22 日到 11 月 28 日不规律。

您还可以使用 Oracle 内置调度程序。通常它用于控制SCHEDULER JOBS但我看不出任何理由不应该将其用于其他用途。

首先创建一个联邦假日列表，例如：

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'CHRISTMAS', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=1225', comments => 'December 25');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'COLUMBUS_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=OCT;BYDAY=2 MON', comments => '2nd Monday in October');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'INDEPENDENCE_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0704', comments => 'July 4');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'MARTIN_LUTHER_KING_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3 MON', comments => '3rd Monday in January');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'MEMORIAL_DAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1 MON', comments => 'Last Monday of May');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'NEW_YEARS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0101', comments => 'January 1');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'THANKSGIVING', repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=4 THU', comments => '4th Thursday in November');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'WASHINGTONS_BIRTHDAY', repeat_interval => 'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3 MON', comments => '3rd Monday in February');
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'WEEKEND', repeat_interval => 'FREQ=DAILY;INTERVAL=1;BYDAY=SAT,SUN');
-- Combined schedule for all federal holidays 
DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'FEDERAL_HOLIDAYS', repeat_interval => 'FREQ=DAILY;INTERSECT=CHRISTMAS,INDEPENDENCE_DAY,MARTIN_LUTHER_KING_DAY,MEMORIAL_DAY,NEW_YEARS_DAY,THANKSGIVING,WASHINGTONS_BIRTHDAY');
END;
/

查看日历语法，了解如何指定repeat_interval。

然后，您可以使用程序DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING来获取日期：

CREATE OR REPLACE FUNCTION LAST_BUSINESS_DAY(today IN TIMESTAMP DEFAULT SYSTIMESTAMP) RETURN TIMESTAMP AS 
return_date_after TIMESTAMP := TRUNC(today);
next_run_date TIMESTAMP;
BEGIN
LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;INTERVAL=1;EXCLUDE=FEDERAL_HOLIDAYS,WEEKEND', NULL, return_date_after, next_run_date);
EXIT WHEN next_run_date >= LAST_DAY(TRUNC(today));
return_date_after := next_run_date;
END LOOP;
RETURN return_date_after;   
END LAST_BUSINESS_DAY;