CREATE OR REPLACE PROCEDURE populate_time_table (v_start_date IN date, v_end_date IN date)
as
/*
DESCRIPTION:
This procedure will populate the TIME table completely,
based on entering a start and end date for the table data.

REVISIONS:
Ver Date Author Description
——— ———- ————— ————————————
1.0 26/06/2002 DRC 1. Created this procedure.

PARAMETERS:
NOTES:
*/

v_day number(2);
v_month number(2);
v_year number(4);
v_wk_yr_no number(2);
v_date date;
v_loop_no number(7);
BEGIN
— Deduct 2 dates to get number of days for loop
SELECT TRUNC (v_end_date – v_start_date)
INTO v_loop_no
FROM dual;

v_date := v_start_date;

— Loop to the number of days + 1 to make it inclusive
FOR i in 1 .. (v_loop_no + 1)
LOOP

v_wk_yr_no := to_number(to_char(v_date,’IW’));
v_day := to_number(to_char(v_date,’DD’));
v_month := to_number(to_char(v_Date,’MM’));
v_year := to_number(to_char(v_date,’YYYY’));

INSERT INTO times values(time_id_seq.nextval, v_day, v_month, v_year, v_wk_yr_no, v_date);

v_date := v_date + 1;

END LOOP;
END;
/

How to populate a time table for data warehousing

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

seventy five − sixty nine =