Solved

Request Oracle SQL Code for DW Calendar Table + Import Data

Posted on 2007-04-11
7
948 Views
Last Modified: 2013-12-18
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!
0
Comment
Question by:hypermac
7 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 18895292
>>Calendar Table
What is the structure that you  are looking for?
How many years data?
0
 

Author Comment

by:hypermac
ID: 18897808
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
 
LVL 27

Accepted Solution

by:
sujith80 earned 168 total points
ID: 18903636
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
 
LVL 15

Assisted Solution

by:ishando
ishando earned 166 total points
ID: 18903640
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
 
LVL 3

Assisted Solution

by:arun04
arun04 earned 166 total points
ID: 19033578
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trigger usage 2 67
Need a starter for ETL protocol? 4 55
Import and exporting Oracle Data with encrypted columns 4 26
Mysql Left Join Case 10 46
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question