This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 14, 2012 10:30 AM by choti RSS

month table

choti Newbie
Currently Being Moderated
I have month table. The data is not showing properly in month number in the quarter (MN_in_quarter). In April the mn_in_quarter should be 1 but it is showing 2. And also i wanted to display two digit numbers for it. example 01,02,03 etc.
For month number it should be two digit. Please correct my query.
--temp table
create table temp_month_vj
(Month_Key NUMBER --sequence
,MN_in_Year NUMBER--month number|| year
,MN_in_Quarter_and_Year VARCHAR2(128) --month number|| quarternumber|| year
,Month_Number NUMBER--month number
,Month_Name VARCHAR2(128)
,MN_in_Quarter NUMBER)--month number in quarter level. for example: apri is quarter 2. out put is month number||quarternumber 0102


CREATE SEQUENCE temp_mont_vj_s
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

INSERT INTO     xddw.temp_month_vj
( Month_Key
, MN_in_Year
, MN_in_Quarter_and_Year
, Month_Number
,Month_Name
,MN_in_Quarter
)
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  
temp_mont_vj_s.nextval
,to_char(EXTRACT(MONTH FROM date_key),'09')||TO_CHAR(date_key, 'YYYY')
,to_char(trunc(to_char(date_key,'mm')/to_char(date_key,'Q')),'09')||TRIM(to_char(TRUNC(TO_CHAR(date_key, 'Q')),'09'))||TO_CHAR(date_key, 'YYYY')
 ,to_char(EXTRACT(MONTH FROM date_key),'09')
,TO_CHAR(date_key , 'MONTH')
,to_char(trunc(to_char(date_key,'mm')/to_char(date_key,'Q')),'09')
FROM     all_days ;
  • 1. Re: month table
    sb92075 Guru
    Currently Being Moderated
    choti wrote:
    I have month table. The data is not showing properly in month number in the quarter (MN_in_quarter). In April the mn_in_quarter should be 1 but it is showing 2. And also i wanted to display two digit numbers for it. example 01,02,03 etc.
    For month number it should be two digit. Please correct my query.
    NUMBERS do NOT have leading (leftmost) zero.
    You must use TO_CHAR() to format the presented string to include leftmost ZERO.
  • 2. Re: month table
    choti Newbie
    Currently Being Moderated
    I did that in the query but it is not taking it
  • 3. Re: month table
    sb92075 Guru
    Currently Being Moderated
    choti wrote:
    I did that in the query but it is not taking it
    If you absolutely insist that the leftmost ZERO be stored in the DB then the columns must be VARCHAR2; not NUMBER


    NUMBER datatype do NOT store leftmost zeros.
  • 4. Re: month table
    choti Newbie
    Currently Being Moderated
    my query is not working to display month number in quarter that is my actual problem can you please help me.
     to_char(trunc(to_char(date_key,'mm')/to_char(date_key,'Q')),'09')
    Edited by: choti on Nov 13, 2012 12:46 PM

    Edited by: choti on Nov 13, 2012 12:55 PM
  • 5. Re: month table
    Solomon Yakobson Guru
    Currently Being Moderated
    choti wrote:
    my query is not working to display quarter number can you please help me.
    Use:
    INSERT INTO     temp_month_vj
    ( Month_Key
    , MN_in_Year
    , MN_in_Quarter_and_Year
    , Month_Number
    ,Month_Name
    ,MN_in_Quarter
    )
    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  temp_mont_vj_s.nextval,
            to_char(date_key,'MMYYYY'),
            to_char(mod(to_char(date_key,'mm') - 1,3) + 1,'00') || TO_CHAR(date_key,'QYYYY'),
            to_char(date_key,'mm'),
            TO_CHAR(date_key ,'FMMONTH'),
            mod(to_char(date_key,'mm') - 1,3) + 1
    FROM     all_days ;
    For example:
    SQL> INSERT INTO temp_month_vj
      2  ( Month_Key
      3  , MN_in_Year
      4  , MN_in_Quarter_and_Year
      5  , Month_Number
      6  ,Month_Name
      7  ,MN_in_Quarter
      8  )
      9  WITH   extrema AS
     10  (
     11   SELECT TO_DATE('01.01.2006','DD.MM.YYYY') AS first_date
     12   , TO_DATE('31.12.2007','DD.MM.YYYY') AS last_date
     13   FROM dual
     14  )
     15  , all_days AS
     16  (
     17   SELECT first_date + LEVEL - 1 AS date_key
     18   FROM extrema
     19   CONNECT BY LEVEL <= last_date + 1 - first_date
     20  )
     21  SELECT  temp_mont_vj_s.nextval,
     22          to_char(date_key,'MMYYYY'),
     23          to_char(mod(to_char(date_key,'mm') - 1,3) + 1,'00') || TO_CHAR(date_key,'QYYYY'),
     24          to_char(date_key,'mm'),
     25          TO_CHAR(date_key ,'FMMONTH'),
     26          mod(to_char(date_key,'mm') - 1,3) + 1
     27  FROM all_days ;
    
    730 rows created.
    
    SQL> select  *
      2    from  temp_month_vj
      3    where mod(month_key,28) = 0
      4  /
    
      MONTH_KEY  MN_IN_YEAR MN_IN_QUARTER_AND_YE MONTH_NUMBER MONTH_NAME  MN_IN_QUARTER
    ----------- ----------- -------------------- ------------ ----------- -------------
             28       12006  0112006                        1 JANUARY                 1
             56       22006  0212006                        2 FEBRUARY                2
             84       32006  0312006                        3 MARCH                   3
            112       42006  0122006                        4 APRIL                   1
            140       52006  0222006                        5 MAY                     2
            168       62006  0322006                        6 JUNE                    3
            196       72006  0132006                        7 JULY                    1
            224       82006  0232006                        8 AUGUST                  2
            252       92006  0332006                        9 SEPTEMBER               3
            280      102006  0142006                       10 OCTOBER                 1
            308      112006  0242006                       11 NOVEMBER                2
    
      MONTH_KEY  MN_IN_YEAR MN_IN_QUARTER_AND_YE MONTH_NUMBER MONTH_NAME  MN_IN_QUARTER
    ----------- ----------- -------------------- ------------ ----------- -------------
            336      122006  0342006                       12 DECEMBER                3
            364      122006  0342006                       12 DECEMBER                3
            392       12007  0112007                        1 JANUARY                 1
            616       92007  0332007                        9 SEPTEMBER               3
            644      102007  0142007                       10 OCTOBER                 1
            672      112007  0242007                       11 NOVEMBER                2
            700      122007  0342007                       12 DECEMBER                3
            728      122007  0342007                       12 DECEMBER                3
            420       22007  0212007                        2 FEBRUARY                2
            448       32007  0312007                        3 MARCH                   3
            476       42007  0122007                        4 APRIL                   1
    
      MONTH_KEY  MN_IN_YEAR MN_IN_QUARTER_AND_YE MONTH_NUMBER MONTH_NAME  MN_IN_QUARTER
    ----------- ----------- -------------------- ------------ ----------- -------------
            504       52007  0222007                        5 MAY                     2
            532       62007  0322007                        6 JUNE                    3
            560       72007  0132007                        7 JULY                    1
            588       82007  0232007                        8 AUGUST                  2
    
    26 rows selected.
    
    SQL> 
    SY.
  • 6. Re: month table
    choti Newbie
    Currently Being Moderated
    Thank you so much. it simplifies my query.
  • 7. Re: month table
    Frank Kulash Guru
    Currently Being Moderated
    choti wrote:
    I have month table. The data is not showing properly in month number in the quarter (MN_in_quarter). In April the mn_in_quarter should be 1 but it is showing 2. And also i wanted to display two digit numbers for it. example 01,02,03 etc.
    Why should it be 1? The comment in the code below suggests that you don't want either 1 or 2, but rather 0201.
    For month number it should be two digit. Please correct my query.
    Mn_in_quarter is a NUMBER. 2 and 02 are exactly the same number; there is no difference between them.
    If you want to make sure that mn_in_quarter is displayed with 2 digits, use
    TO_CHAR (mn_in_quarter, 'FM00')
    when displaying it. If all you ever do with this column is display it, you might make that column a VARCHAR2 instead of a NUMBER.

    Perhaps you want something like this:
    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    temp_mont_vj_s.nextval          AS month_key
    ,       to_char (EXTRACT(MONTH FROM date_key),'09')
           ||  TO_CHAR (date_key, 'YYYY')     AS mn_in_year
    ,       to_char (trunc(to_char(date_key,'mm')/to_char(date_key,'Q')),'09')
           ||  TRIM(to_char(TRUNC(TO_CHAR(date_key, 'Q')),'09'))
           || TO_CHAR(date_key, 'YYYY')          AS mn_in_quarter_and_year
     ,       to_char(EXTRACT(MONTH FROM date_key),'09')
                                  AS month_number
    ,       TO_CHAR(date_key , 'MONTH')          AS month_name
    ,       TO_CHAR ( 1 + MONTHS_BETWEEN ( TRUNC (date_key, 'MONTH')
                                       , TRUNC (date_key, 'Q')
                               )
                , 'FM00'
                )
          ||  TO_CHAR ( date_key
                        , '"0"Q'
                )                    AS mn_in_quarter
    FROM     all_days ;
    Aside from formatting, the only change I made was in the mn_in_quarter column of the main query.
    You may want to change the mn_in_quarter_and_year column, too.
  • 8. Re: month table
    choti Newbie
    Currently Being Moderated
    MN_IN_QUARTER_AND_YE MONTH_NUMBER the quarter number is not displaying with 01,02,03 etc. can you please help me with this insert. and also also last column too
  • 9. Re: month table
    choti Newbie
    Currently Being Moderated
    Great this is what i am looking for.
  • 10. Re: month table
    choti Newbie
    Currently Being Moderated
    Hi Frank,

    With the following query i am inserting duplicate records in the table.

    How can i insert distinct records in the table. Each month should have only one record.

    Thanks
  • 11. Re: month table
    sb92075 Guru
    Currently Being Moderated
    choti wrote:
    Hi Frank,

    With the following query i am inserting duplicate records in the table.

    How can i insert distinct records in the table. Each month should have only one record.
    a Unique Index avoids duplicates
  • 12. Re: month table
    choti Newbie
    Currently Being Moderated
    on all the columns
  • 13. Re: month table
    sb92075 Guru
    Currently Being Moderated
    choti wrote:
    on all the columns
    no
  • 14. Re: month table
    choti Newbie
    Currently Being Moderated
    How can we insert a unique record inside the table. with the current insert script it is inserting duplicate records. please help me to resolve this
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points