Oracle obtains the date after the specified workday Extension

Oracle may have this requirement to obtain the date after the specified working day. You want to obtain the specific date after the delay (excluding weekends and holidays) based on the start time and the number of days after the specified working day ), for example, get the date after 5 working days based on the date 2012-08-27 and get the specific date 2012-09-03. What should we do in this case? This article will solve this problem from the database point of view. Of course, it is easier to use code in the logic !! Note: The application in this article is oracle10;
Step 1: Create a holiday form for flexible storage of holiday dates (excluding weekends). If it is the same as the weekend date, you do not need to add it to the form: SQL code create table HOLIDAY (ID VARCHAR2 (45) not null, HL_DATE DATE, HL_DESC VARCHAR2 (500), REMARK VARCHAR2 (500), constraint PK_HOLIDAY primary key (ID )); www.2cto.com comment on table HOLIDAY is 'holiday date table (excluding weekends) '; comment on column HOLIDAY. ID is 'holiday number'; comment on column HOLIDAY. HL_DATE is 'holidays '; comment on column HOLIDAY. HL_DESC is 'holiday description'; comment on column HOLIDAY. REMARK is 'note'; add some test data: ID HL_DATE HL_DESC1 2012-9-29 11 2 2012-10-1 11 3 2012-10-2 11 4 2012-10-3 11 5 2012-10-4 11 6 2012-10-5 11 7 2012-9-3 Test 8 2012-9-5 test 9 2012-9-6 test www.2cto.com Step 2: write a function to obtain the number of weekend days between two dates, as shown in the following SQL code: create or replace function weekend_count (startdate date, enddate date) return number/** return the number of weekend days in two time periods. The start date is not counted. */is weekend_num number: = 0; tempdate date; begin tempdate: = startdate + 1; while tempdate <= enddate loop weekend_num: = weekend_num + (case when to_char (tempdate, 'day') in ('satur', 'sunday') then 1 else 0 end ); tempdate: = tempdate + 1; end loop; return weekend_num; end;
Step 3: Write a function to get the specific date after the specified date is delayed for a specific business day, as follows: SQL code create or replace function getDeferDate (startdate date, deferDay number) return date/** return the extended date */is enddate date: = startdate + deferDay; -- the extended date holiday_num number: = 0; -- holiday days weekend_num: = 0; -- weekend days nonework_num number: = 0; -- Non-workday days begin
-- Get weekend days weekend_num: = weekend_count (startdate, enddate); -- get holiday days select nvl (count (1), 0) into holiday_num from holiday a where. hl_date is not null and. hl_date between startdate and enddate; nonework_num: = weekend_num + holiday_num; www.2cto.com if nonework_num! = 0 then enddate: = getDeferDate (enddate, nonework_num );
End if; return enddate; end; Step 4: actual application, as shown in the following figure: SQL code select getdeferdate (sysdate, 5) deferdate from dual; Result: DEFERDATE1 16:12:53