Solved

Request Oracle SQL Code for DW Calendar Table + Import Data

Posted on 2007-04-11
7
945 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
compre toata in where clue oracle 4 41
sql query 5 52
Error executing command from server 6 26
Clone Oracle 12c Database 5 26
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now