Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Question Unanswered: getting relative year into view

    I have a view defined on a calendertable 'TIME' that consists of 5 columns. I want to somehow add the relative year as a calculated 6th column, without changing the underlying table. The catch is that we work with fiscal years and months. A fiscal year starts at 1-12 of a year and ends on 30-11.
    I am struggling to create a statement for the relative year like CY for the current year, 1M for last fiscal year , etc.
    Anyone an idea?

    This is the view definition as yet:

    CREATE VIEW TARGET.TIME_DIST_PERIOD
    (FISC_YEAR,
    FISC_MONTH,
    FISC_PERIOD,
    FISC_PERIOD_REL,
    MONTHNAME
    )
    AS Select DISTINCT FISCAL_YEAR,FISCAL_MONTH, FISCAL_PERIOD, FISCAL_PERIOD_REL,
    CASE FISCAL_MONTH
    WHEN 1
    THEN 'DEC'
    WHEN 2
    THEN 'JAN'
    WHEN 3
    THEN 'FEB'
    WHEN 4
    THEN 'MAR'
    WHEN 5
    THEN 'APR'
    WHEN 6
    THEN 'MAY'
    WHEN 7
    THEN 'JUN'
    WHEN 8
    THEN 'JUL'
    WHEN 9
    THEN 'AUG'
    WHEN 10
    THEN 'SEP'
    WHEN 11
    THEN 'OCT'
    WHEN 12
    THEN 'NOV'
    ELSE NULL
    END AS MONTHNAME
    from TARGET.TIME;

    COMMENT ON TABLE TARGET.TIME_DIST_PERIOD
    IS 'View with distinct values of fisc_period etc from TIME table';
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: getting relative year into view

    See if the function MONTHNAME will come in handy ...

    Cheers

    Sathyaram

    Originally posted by blom0344
    I have a view defined on a calendertable 'TIME' that consists of 5 columns. I want to somehow add the relative year as a calculated 6th column, without changing the underlying table. The catch is that we work with fiscal years and months. A fiscal year starts at 1-12 of a year and ends on 30-11.
    I am struggling to create a statement for the relative year like CY for the current year, 1M for last fiscal year , etc.
    Anyone an idea?

    This is the view definition as yet:

    CREATE VIEW TARGET.TIME_DIST_PERIOD
    (FISC_YEAR,
    FISC_MONTH,
    FISC_PERIOD,
    FISC_PERIOD_REL,
    MONTHNAME
    )
    AS Select DISTINCT FISCAL_YEAR,FISCAL_MONTH, FISCAL_PERIOD, FISCAL_PERIOD_REL,
    CASE FISCAL_MONTH
    WHEN 1
    THEN 'DEC'
    WHEN 2
    THEN 'JAN'
    WHEN 3
    THEN 'FEB'
    WHEN 4
    THEN 'MAR'
    WHEN 5
    THEN 'APR'
    WHEN 6
    THEN 'MAY'
    WHEN 7
    THEN 'JUN'
    WHEN 8
    THEN 'JUL'
    WHEN 9
    THEN 'AUG'
    WHEN 10
    THEN 'SEP'
    WHEN 11
    THEN 'OCT'
    WHEN 12
    THEN 'NOV'
    ELSE NULL
    END AS MONTHNAME
    from TARGET.TIME;

    COMMENT ON TABLE TARGET.TIME_DIST_PERIOD
    IS 'View with distinct values of fisc_period etc from TIME table';

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sathyaram, it won't

    blom0344, can you explain what you mean a bit more?

    "...struggling to create a statement for the relative year like CY for the current year, 1M for last fiscal year , etc."

    the table already contains the fiscal year

    e.g. in december 2004, fiscal year will be 2005

    so 2005 will be in the FISCAL_YEAR column, right?

    so what are you trying to do? convert the fiscal year to the "real" year?

    if so, the current year would be

    CASE FISCAL_MONTH
    WHEN 1 THEN FISCAL_YEAR - 1
    ELSE FISCAL_YEAR END

    i think


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    To explain in more detail....

    Imagine building reports with an BI tool. It would be very convenient not having to code the current year as 2003, cause next year every report has to be updated in that respect. So enter the relative year CY. Using this in variables / conditions for current year will cause the report to keep functioning without intervention when 2003 turns into 2004.

    My time table is generated every first of the month resetting the current month. I can think of changing the table , but this will have effect on lots of other stuff, so I tried to deal with by creating a view on the table.

    I'll attach the table to give you an idea........
    Attached Files Attached Files
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, thanks, that table is beautiful, and i have seen tables like it many times

    should be no problem defining a view to create whatever value you want

    can you please explain again what it is you want?

    i'm not sure what you mean by "relative year"

    the table already appears to contain both the current year and the fiscal year


    rudy

  6. #6
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Look at the table that I attached , notice that for all the dates in the current month March the field rel_month reads 'CM'. In April all dates in April will have the field rel_month filled with 'CM'. The dates in March will then be filled with '1M' .......
    This effect is accomplished by rebuilding the TIME table every first day of the month very early in the morning. (I use an ETL tool to do that job)

    In the view I only want months and years, not individual dates (hence the distinct clause) because my datamart only contains data by month and year. So I want to add a column to the view that should read 'CY' for all months in 2003, because we now live in 2003. So 2002 will then read '1Y', 2001 '2Y' etc.

    Of course in 2004 'CY' should be the relative year for 2004 and '1M' for 2003. Because a view builds itself I thought to handle the relative year as a view column instead of changing the structure of the underlying table...........
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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