I have a date table to load. I have the following script to load. Is there any way to simplify the insert script. all the data should have two digits example quarter should be 01,02 etc. month should be 01,02 day should be 01 02 etc year is 4 digit
create table xddw.temp_date_vj
(Date_Key DATE
,Day_Name VARCHAR2(128)
,DN_in_Month NUMBER
,DN_in_Quarter NUMBER
,DN_in_Year NUMBER
,DN_in_Month_and_Year VARCHAR2(128)
,DN_in_Quarter_and_Year VARCHAR2(128)
,DN_in_Mon_and_Quart_and_Year VARCHAR2(128)
,Weekend VARCHAR2(128)
,Weekday VARCHAR2(128))
INSERT INTO xddw.temp_date_vj
( date_key
, dn_in_month
, dn_in_quarter
, dn_in_year
,day_name
,dn_in_month_and_year
,DN_IN_QUARTER_AND_YEAR
,DN_IN_MON_AND_QUART_AND_YEAR
,WEEKEND
,weekday
)
WITH extrema AS
(
SELECT TO_DATE('01.01.2006','DD.MM.YYYY') AS first_date
, TO_DATE('31.12.2007','DD.MM.YYYY') AS last_date
FROM dual
)
, all_days AS
(
SELECT first_date + LEVEL - 1 AS date_key
FROM extrema
CONNECT BY LEVEL <= last_date + 1 - first_date
)
SELECT date_key
, date_key + 1 - TRUNC (date_key, 'MONTH')
, date_key + 1 - TRUNC (date_key, 'Q')
, date_key + 1 - TRUNC (date_key, 'YEAR')
, TO_CHAR(date_key , 'DAY')
,TO_CHAR(date_key, 'DD')||TO_CHAR(date_key, 'MM')||TO_CHAR(date_key, 'YYYY')
,to_char(TRUNC(date_key + 1 - TRUNC (date_key, 'Q')),'09')||TRIM(to_char(TRUNC(TO_CHAR(date_key, 'Q')),'09'))||TO_CHAR(date_key, 'YYYY')
,TO_CHAR(date_key, 'DD')||TO_CHAR(date_key, 'MM')||TRIM(to_char(TRUNC(TO_CHAR(date_key, 'Q')),'09'))||TO_CHAR(date_key, 'YYYY')
,DECODE(DECODE(TO_CHAR(date_key, 'D'), '7', 'WEEKEND', '1', 'WEEKEND' ,'WEEKDAY'),'WEEKEND','1','0')
,DECODE(DECODE(TO_CHAR(date_key, 'D'), '7', 'WEEKEND', '1', 'WEEKEND' ,'WEEKDAY'),'WEEKDAY','1','0')
FROM all_days ;
Edited by: choti on Nov 13, 2012 1:38 PM