load a table with relative days, weeks, and months
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
A solution using DBMS_CRYPTO and DBMS_SCHEDULER
The
CREATE SEQUENCE dim_date_seq;

CREATE TABLE dim_date (
dim_date_key                INTEGER,
full_date                   DATE NOT NULL,
day_number                  VARCHAR2(2)  NOT NULL,
month_long_desc             VARCHAR2(9)  NOT NULL,
month_number                VARCHAR2(2)  NOT NULL,
month_short_desc            VARCHAR2(3)  NOT NULL,
quarter_long_desc           VARCHAR2(30) NOT NULL,
quarter_number              INTEGER      NOT NULL,
quarter_short_desc          VARCHAR2(20) NOT NULL,
relative_day                INTEGER,
relative_month              INTEGER,
relative_month_short_desc   VARCHAR2(10),
relative_quarter            INTEGER,
relative_quarter_short_desc VARCHAR2(20),
relative_week               INTEGER,
relative_week_short_desc    VARCHAR2(20),
relative_year               INTEGER,
relative_year_desc          VARCHAR2(20),
relative_year_month_desc    VARCHAR2(15),
week_long_desc              VARCHAR2(20) NOT NULL,
week_month_number           INTEGER      NOT NULL,
week_short_desc             VARCHAR2(10) NOT NULL,
week_year_number            VARCHAR2(4)  NOT NULL,
year_month_str              VARCHAR2(8)  NOT NULL,
year_number                 VARCHAR2(4)  NOT NULL,
create_date                 DATE         NOT NULL,
last_update_date            DATE)        NOT NULL;

ALTER TABLE dim_date
ADD CONSTRAINT pk_dim_date
PRIMARY KEY (dim_date_key);

ALTER TABLE dim_date
ADD CONSTRAINT uk_dim_date
UNIQUE (full_date);
L
CREATE OR REPLACE PROCEDURE load_dim_date AUTHID DEFINER IS
 d_data  dim_date%ROWTYPE;
 CurDate DATE := TO_DATE('01-NOV-2011');
BEGIN
  FOR i IN 0 .. 999 LOOP
    d_data.dim_date_key := dim_date_seq.NEXTVAL;
    d_data.full_date := CurDate;
    d_data.dim_date_key := dim_date_seq.NEXTVAL;
    d_data.full_date := CurDate;
    d_data.day_number := TO_CHAR(CurDate, 'DD');
    d_data.month_long_desc := INITCAP(TO_CHAR(CurDate, 'MONTH'));
    d_data.month_number := TO_CHAR(CurDate, 'MM');
    d_data.month_short_desc := INITCAP(TO_CHAR(CurDate, 'MON'));
    d_data.quarter_long_desc := 'Quarter ' || TO_CHAR(CurDate, 'Q') || ', ' ||
                                TO_CHAR(CurDate, 'YYYY');
    d_data.quarter_number := TO_CHAR(CurDate, 'Q');
    d_data.quarter_short_desc := 'Q' || TO_CHAR(CurDate, 'Q') || ' ' || TO_CHAR(CurDate, 'YYYY');
    d_data.week_long_desc := 'Week ' || TO_CHAR(CurDate, 'WW');
    d_data.week_month_number := TO_NUMBER(TO_CHAR(CurDate, 'WW'));
    d_data.week_short_desc := 'W ' || TO_CHAR(CurDate, 'WW');
    d_data.week_year_number := TO_CHAR(CurDate, 'WW');
    d_data.year_month_str := TO_CHAR(CurDate, 'YYYY') || '/' || INITCAP(TO_CHAR(CurDate, 'MON'));
    d_data.year_number := TO_CHAR(CurDate, 'YYYY');
    d_data.create_date := CurDate;
    d_data.last_update_date := CurDate;

    INSERT INTO dim_date VALUES d_data;

    CurDate := CurDate + 1;
  END LOOP;
  COMMIT;
END load_dim_date;
/
L
CREATE OR REPLACE PROCEDURE calc_relative_dim_date AUTHID DEFINER IS
 CURSOR crcur IS
 SELECT dim_date_key, full_date
 FROM dim_date
 FOR UPDATE;

 vRelativeDay dim_date.relative_day%TYPE;
 vRelativeweek dim_date.relative_week%TYPE;

 CurDate DATE := TRUNC(SYSDATE);
 WkBegJDate INTEGER := TO_NUMBER(TO_CHAR(CurDate-6, 'J'));
BEGIN
  FOR crrec IN crcur LOOP
    vRelativeDay := crrec.full_date-TRUNC(CurDate);
    vRelativeWeek := FLOOR((TO_NUMBER(TO_CHAR(crrec.full_date, 'J') - WkBegJDate))/7);

    UPDATE dim_date
    SET relative_day = vRelativeDay,
        relative_week = vRelativeWeek
    WHERE CURRENT OF crcur;
  END LOOP;
  COMMIT;
END calc_relative_dim_date;
/
A
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved