8 Replies Latest reply: Nov 14, 2012 7:38 AM by SomeoneElse RSS

    date table

    choti
      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
        • 1. Re: date table
          Frank Kulash
          choti wrote:
          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
          If you want the actual values stored in the table to have leading 0's, then you have to make them VARCHAR2 columns, not NUMBERs.
            
          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')
          Please format your code, to make it readable.
            
          ,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')
          You can format the quarter and the year in one TO_CHAR call:
          ,   TO_CHAR ( TRUNC ( date_key 
                                  + 1 
                        - TRUNC (date_key, 'Q')
                        )
                   , '09')
           ||  TO_CHAR (date_key, '"0"QYYYY')     AS dn_in_quarter_and_year
            
          ,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 ;
          The weekend and weekday columns are much more complicated than they need to be.
          I would use something like
          CASE  
              WHEN  TO_CHAR (date_key, 'DY')  IN ('SAT', 'SUN')
              THEN  '1'
              ELSE  '0'
          END          AS weekend
          If the only possible values for weekend and weekday are '0' and '1', why do you define the columns as VARCHAR2 (128)? Wouldn't VARCHAR2 (1) be enough?
          • 2. Re: date table
            choti
            Ok i will change it to varchar2. is my query looks good ?
            • 3. Re: date table
              Frank Kulash
              Hi,
              choti wrote:
              Ok i will change it to varchar2. is my query looks good ?
              No, it looks horrible. Format it, so you can easily see what each expression is doing, and what the arguments to each function are.
              • 4. Re: date table
                SomeoneElse
                , TO_CHAR(date_key , 'DAY')
                Be careful here.

                This will always return the same length column with trailing spaces regardless of the day.

                You might want to trim it (or use fm).
                • 5. Re: date table
                  choti
                  How to display this column
                  DN_in_Mon_and_Quart_and_Year
                  example 01.01.2006

                  should be daynumber||monthnumber||quarternumber||year
                  0101012006
                  • 6. Re: date table
                    Solomon Yakobson
                    choti wrote:
                    How to display this column
                    SQL> select  to_char(date '2006-01-01','DDMM"0"QYYYY')
                      2    from  dual
                      3  /
                    
                    TO_CHAR(DA
                    ----------
                    0101012006
                    
                    SQL> 
                    SY.
                    • 7. Re: date table
                      EdStevens
                      choti wrote:
                      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
                      I'm rather curious as to why no one questioned the purpose of this table. Since everything in it is derived from a simple DATE, why bother storing it at all? After all, isn't one of the basic rules of database design is to NOT store data that can be derived on demand from other data?

                      I'll admit that there could be valid reasons, but I would think that before working up a technical solution, one should first determine if the OP isn't asking the wrong question.
                      • 8. Re: date table
                        SomeoneElse
                        I've worked on data warehouses that have a date dimension table.

                        They're quite handy. Especially when it comes to things that aren't so easy to derive, like company holidays and business days.