Request Oracle SQL Code for DW Calendar Table + Import Data

Hello Gurus,
I don't want to reinvent the wheel so can someone please either send me or point me to SQL code [Oracle, please] that will create a basic Calendar table for a data warehouse and populate it for years out.  I've had this type of code in my hands once before and can't find it anywhere!  You'll save me hours of work!

Thanks!
Cheryl McCormickConsultantAsked:
Who is Participating?
 
SujithData ArchitectCommented:
This script will generate data for 1 year.

select trunc(sysdate + lvl) sys_date,
       to_char(sysdate+lvl, 'J') julian_date,
       to_char(sysdate+lvl, 'mm') month,
       to_char(sysdate+lvl, 'q') quarter,
       trunc(sysdate+lvl, 'month') mnth_st_date,
       trunc(last_day(sysdate+lvl)) mnth_end_date
from
(select level lvl from dual connect by level <= 365 * 1)
/

-- You can change sysdate to start from a previous date.
0
 
SujithData ArchitectCommented:
>>Calendar Table
What is the structure that you  are looking for?
How many years data?
0
 
Cheryl McCormickConsultantAuthor Commented:
Thanks for the reply, sujith80!  The Calendar should contain the date, julian date, month, year, month start date, month end date, QTR...I owuld also like the option to create a set of fiscal columns -->  fiscalmonth, fiscalyear, fiscalQTR depending on either a variable or an update script.  If you have a calendar with more columns, no worries.  More is not a problem.  

Thanks again!
0
 
ishandoCommented:
creating the table is just a simple create statement....

populating it can be done with something like:

declare
  start_dt date := to_date('20010101','yyyymmdd');
  end_dt date := to_date('20011231','yyyymmdd');
  uDt date;
  dys integer := end_dt - start_dt;
begin
  for i in 0 .. dys loop
    uDt := start_dt + i;
    insert into cal_tab(cDate, cJul, cMon, cYr, cMonStrt, cMonEnd, cQtr)
    values (uDt, to_char(uDt,'J'), to_char(uDt,'mm'), to_char(uDt,'yyyy'), trunc(uDt,'mon'), last_day(uDt), to_char(uDt,'Q'));
  end loop;
  commit;
end;
/

0
 
arun04Commented:
CREATE TABLE holiday_calendar
(
ID NUMBER NOT NULL ,
actual_date DATE NOT NULL ,
YEAR NUMBER NOT NULL  ,
quarter NUMBER NOT NULL  ,
MONTH NUMBER NOT NULL  ,
week NUMBER NOT NULL  ,
DAY NUMBER NOT NULL  ,
day_of_week NUMBER NOT NULL,
month_name VARCHAR2(30) NOT NULL,
day_name VARCHAR2(30) NOT NULL  ,
is_weekend NUMBER(1) DEFAULT 0 NOT NULL  ,
is_holiday NUMBER(1) DEFAULT 0 NOT NULL  ,
holiday_desc VARCHAR2(100)
);

and populate from 2006..2015 -- you can change this

/* Formatted on 2007/04/20 16:06 (Formatter Plus v4.8.8) */
DECLARE
   j            NUMBER;
   start_date   NUMBER := 2453737;
   new_date     DATE;
   i            NUMBER := 0;
BEGIN
   FOR l1 IN 2006 .. 2015
   LOOP
      IF is_leap_year (l1)
      THEN
         j := 366;
      ELSE
         j := 365;
      END IF;

      FOR l2 IN 1 .. j
      LOOP
         new_date := TO_DATE (start_date + i, 'J');

         INSERT INTO holiday_calendar
                     (ID, actual_date, YEAR, quarter, MONTH, week, DAY,
                      day_of_week, month_name, day_name, is_weekend,
                      is_holiday, holiday_desc)
            SELECT entityid.NEXTVAL, new_date, TO_CHAR (new_date, 'rrrr'),
                   TO_CHAR (new_date, 'Q'), TO_CHAR (new_date, 'mm'),
                   TO_CHAR (new_date, 'WW'), TO_CHAR (new_date, 'dd'),
                   TO_CHAR (new_date, 'd'), TO_CHAR (new_date, 'Month'),
                   TO_CHAR (new_date, 'Day'),
                   DECODE (TO_CHAR (new_date, 'd'), 1, 1, 7, 1, 0), 0, NULL
              FROM DUAL;

         i := i + 1;
      END LOOP;
   END LOOP;
END;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.