I had to generate a weekly calendar that had every week starting on the previous Saturday of a given date and ending on the next Friday.
The following procedure solved my problem. The procedure takes two parameters:
- PROCESS_START_DT (DATE)
- PROCESS_END_DT (DATE)
that define the time interval for which the calendar needs to be generated.
I.e. If PROCESS_START_DT = '01-Jul-2017' and PROCESS_END_DT = '31-Jul-2017', the expected result will be:
The result will be stored in a dedicated table.
The key is to determine the day of the week given a date, in order to establish the previous Saturday and next Friday. To do that, we use the TRUNC(cut_date, 'IW') which will return the first day of the ISO week. Using simply TRUNC would make the code dependent on the local NLS parameters.
To loop from the Start Date to the End Date, we convert the dates to the julian format (to_number(to_char(PROCESS_START_DT,'j'))) and we implement a numeric loop starting from the PROCESS_START_DT (temporarly stored in cut_date variable) and we increment it by 7 days at each iteration (cut_date := cut_date + 7).
create or replace PROCEDURE PR_XXX_XXX_GENERATE_CAL(PROCESS_START_DT DATE, PROCESS_END_DT DATE) AS
cut_date date;
cal_year number;
st_dt date;
end_dt date;
cal_week_nbr number;
table_exists_flg number;
diff_days number;
begin
/* Reset Variables */
cut_date := PROCESS_START_DT;
cal_week_nbr := 1;
select count(*) into table_exists_flg
from all_tables
where table_name = upper('wc_xxx_xxx_calendar');
/* If the calendar table does not exists, then create it */
if table_exists_flg = 0 then
execute immediate 'create table wc_xxx_xxx_calendar
(CAL_YEAR NUMBER,
CAL_WEEK_NBR NUMBER,
EFFECTIVE_START_DT DATE,
EFFECTIVE_END_DT DATE)';
end if;
/* Truncate Calendar Table */
execute immediate 'truncate table wc_xxx_xxx_calendar';
commit;
for i in to_number(to_char(PROCESS_START_DT,'j'))..to_number(to_char(PROCESS_END_DT,'j')) loop
/* Make sure that always previous Saturday and next Friday are retrieved, independently of the DB Localization */
if to_date(i,'j') = cut_date then
/* Removed because it depends on Localization */
/* select EXTRACT(YEAR FROM cut_date), trunc(cut_date, 'd')-2 , trunc(cut_date, 'd')+4 */
/* Calculate difference in days from cut_date and previous Monday */
select cut_date - trunc(cut_date, 'IW') into diff_days
from dual;
if (diff_days >= 5) then
/* cut_date is a Saturday or Sunday - always current Saturday is returned */
select EXTRACT(YEAR FROM cut_date), trunc(cut_date, 'IW')+5 , trunc(cut_date+7, 'IW')+4
into cal_year, st_dt, end_dt
from dual;
else
/* Any weekday - except for Saturday and Sunday */
select EXTRACT(YEAR FROM cut_date), trunc(cut_date, 'IW')-2 , trunc(cut_date, 'IW')+4
into cal_year, st_dt, end_dt
from dual;
end if;
insert into wc_xxx_xxx_calendar values (cal_year, cal_week_nbr, st_dt, end_dt);
COMMIT;
cal_week_nbr := cal_week_nbr + 1;
cut_date := cut_date + 7;
end if;
END LOOP;
END PR_XXX_XXX_GENERATE_CAL;
