Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Unanswered: Time Dimension generation

    Dear All,

    I have to generate time details for the given period

    Input: 20 January 2002 to 31 Dec 2006

    I need to store the following details in the table:

    Output: Year, Quarter, Month, Week

    Does anyone having readymade procedure for this. A pl/sql script has to be generated for the given period.

    How to generate Different year numbers, quarter for that year, month, week etc..respectively for a given period. What are all the necessary steps to be taken.

    Any Ideas of how to do it??

    I would appreciate for an early solution.

  2. #2
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    You can do an insert into tabname (select ... from ... ) to populate your table. I assume you want the individual quarter, month and week numbers. If you want each actual date, then remove the "distinct". A suitable select may be...

    select distinct
           to_char( udate,'YYYY' ) year,
           to_char( udate,'Q' ) quarter,
           to_char( udate,'MM' ) month,
           to_char( udate,'WW' ) week
    from   (
           select to_date('19/01/2002','DD/MM/YYYY')+rownum udate
           from   all_objects
           where  to_date('19/01/2002','DD/MM/YYYY')+rownum <= to_date( '31/12/2006','DD/MM/YYYY')  
    Note the following from the Oracle documentation regarding week and year numbers. You may wish to adjust the formats accordingly.

    The week numbers returned by the WW format mask are calculated according to the algorithm int((day-ijan1)/7). This week number algorithm does not follow the ISO standard (2015, 1992-06-15).

    To support the ISO standard, a format element IW is provided that returns the ISO week number. In addition, format elements I IY IYY and IYYY, equivalent in behavior to the format elements Y, YY, YYY, and YYYY, return the year relating to the ISO week number.

    In the ISO standard, the year relating to an ISO week number can be different from the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. A week always starts on a Monday and ends on a Sunday.

    If January 1 falls on a Friday, Saturday, or Sunday, then the week including January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.

    If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the week is the first week of the new year, because most of the days in the week belong to the new year.

    For example, January 1, 1991, is a Tuesday, so Monday, December 31, 1990, to Sunday, January 6, 1991, is week 1. Thus, the ISO week number and year for December 31, 1990, is 1, 1991. To get the ISO week number, use the format mask "IW" for the week number and one of the "IY" formats for the year.

    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Sep 2003

    Time dimension

    Thanx Bill,

    Hope this solution would solve my problem. I would check with my data and getback to you

    thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts