locked
Advice needed for modeling the financial year. RRS feed

  • Question

  • A database is currently being designed to store data relating to “Key Performance Indicators.” The data represents a budget, stretch and actual value for each month of the financial year. The types of queries to be preformed are:

    SELECT Actual

    FROM Values

    WHERE KPI = 1

    AND SYSDATE = Month/Year

     

    Or

     

    SELECT Budget, Stretch, Actual

    FROM Values

    WHERE KPI = 1

    AND SYSDATE = Financial year

    The problem is how to model the date/financial year. The structure determined* so far seem inappropriate and it is hope someone could offer advise to the best way. Happy to provide more clarification on request.

    Sedulo,

     

    Ian

     *

     ValueID  KPIID     Budget   Actual    Stretch   Date

    -----------------------------------------------------------

    1              1              98            97            .5             Jul-05

    Wednesday, July 12, 2006 7:00 AM

All replies

  • The solution is the use of the user-define data type;
    Ex: you can create a user-define data type by using:

     DECLARE financialYear (@ValueID nvarchar(15), @KPIID nvarchar(15), @Budget nvarchar(15), @Actual nvarchar(15), @Stretch nvarchar(15), @Date datetime (15) )

     CONSTRAINT CheckfinancialYear CHECK (financialYear LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )



    The above code defines some values of the UDT with some constraints(additional to your requirements).

     
    And the accessing/create part as below:

     

    CREATE TABLE YEARBUDJET
    ( fYear as financialYear,
      ......
      .......
      .....
    )

     

    SELECT financialYear
    FROM YEARBUDJET fy
    WHERE fy.KPIID = ‘1’
    AND fy.Date = SYSDATE

     

    I think this will satisfy your requirements.

     

     

    Wednesday, September 6, 2006 9:39 AM