?
Solved

Request Oracle SQL Code for DW Calendar Table + Import Data

Posted on 2007-04-11
7
Medium Priority
?
956 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 672 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 664 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 664 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

800 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