CREATE TABLE mh_time_periods
(
time_period_id NUMBER NOT NULL
,YEAR NUMBER(4) NOT NULL
,quarter NUMBER(1) NOT NULL
,MONTH NUMBER(2) NOT NULL
,DAY NUMBER(2) NOT NULL
,month_end_flag char(1) DEFAULT 'N'
,year_end_flag char(1) default 'N'
,quarter_end_flag CHAR(1) DEFAULT 'N'
,period_date DATE NOT NULL
,CONSTRAINT mh_time_periods_pk PRIMARY KEY(time_period_id)
,CONSTRAINT mh_time_periods_chk1 CHECK(month_end_flag IN ('N','Y'))
,constraint mh_time_periods_chk2 check(year_end_flag in ('N','Y'))
,CONSTRAINT mh_time_periods_chk3 CHECK(quarter_end_flag IN ('N','Y'))
);
/
CREATE SEQUENCE mh_time_period_seq START WITH 1 INCREMENT BY 1;
/
Currently the time dimension table has basic settings. More columns can be added as and when required.
CREATE OR REPLACE PROCEDURE mh_fill_time_data(
p_end_date DATE
)
IS
l_year NUMBER(4) := 2005;
l_month NUMBER(2) := 1;
l_day NUMBER(2) := 1;
l_quarter NUMBER(1) := 0;
--l_flag BOOLEAN := FALSE;
l_month_end_flag CHAR(1) := 'N';
l_year_end_flag char(1) := 'N';
l_quarter_end_flag CHAR(1) := 'N';
l_loop_day_end NUMBER(2);
l_end_year NUMBER(4) := TO_NUMBER(TO_CHAR(p_end_date,'yyyy'));
l_end_month NUMBER(2) := TO_NUMBER(TO_CHAR(p_end_date,'mm'));
l_end_day NUMBER(2) := TO_NUMBER(TO_CHAR(p_end_date,'dd'));
BEGIN
SAVEPOINT start_insert;
LOOP
LOOP
IF l_month IN (1,2,3) THEN
l_quarter := 1;
ELSIF l_month IN (4,5,6) THEN
l_quarter := 2;
ELSIF l_month IN (7,8,9) THEN
l_quarter := 3;
ELSE
l_quarter := 4;
END IF;
IF l_month IN (1,3,5,7,8,10,12) THEN
l_loop_day_end := 31;
ELSIF l_month IN (4,6,9,11) THEN
l_loop_day_end := 30;
ELSE
l_loop_day_end := get_feb_day(l_year);
end if;
IF l_year = l_end_year AND l_month = l_end_month THEN
l_loop_day_end := l_end_day;
END IF;
LOOP
IF l_day = l_loop_day_end THEN
l_month_end_flag := 'Y';
IF l_month = 12 THEN
l_year_end_flag := 'Y';
END IF;
end if;
if l_month_end_flag = 'Y' then
if l_month IN (3,6,9,12) then
l_quarter_end_flag := 'Y';
END IF;
END IF;
INSERT INTO mh_time_periods(
time_period_id
,YEAR
,quarter
,MONTH
,DAY
,month_end_flag
,year_end_flag
,quarter_end_flag
,period_date
)
VALUES(
mh_time_period_seq.NEXTVAL
,l_year
,l_quarter
,l_month
,l_day
,l_month_end_flag
,l_year_end_flag
,l_quarter_end_flag
,TO_DATE(TO_CHAR(l_day)||'-'||TO_CHAR(l_month)||'-'||TO_CHAR(l_year),'DD-MM-YYYY')
);
l_day := l_day + 1;
EXIT WHEN l_day > l_loop_day_end;
END LOOP; -- end of day loop
l_quarter_end_flag := 'N';
l_month_end_flag := 'N';
l_day := 1;
l_month := l_month + 1;
EXIT WHEN l_month > 12 OR (l_year = l_end_year AND l_month > l_end_month);
END LOOP; -- End of month loop
l_year_end_flag := 'N';
l_month := 1;
l_year := l_year + 1;
EXIT WHEN l_year > l_end_year;
END LOOP; -- End of year loop
COMMIT;
dbms_output.put_line('Done');
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
ROLLBACK TO start_insert;
END mh_fill_time_data;
/
CREATE OR REPLACE FUNCTION get_feb_day (nYr IN NUMBER) RETURN NUMBER IS
v_day varchar2(2);
begin
select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;
return to_number(v_day);
END;
/
The above plsql populates from 01-JAN-2005. But can be changed to any starting date.
Note:
The code is open for scrutiny
