• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 962
  • Last Modified:

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!
0
hypermac
Asked:
hypermac
3 Solutions
 
sujith80Commented:
>>Calendar Table
What is the structure that you  are looking for?
How many years data?
0
 
hypermacAuthor 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
 
sujith80Commented:
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now