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




    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.





     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:


    ( fYear as financialYear,


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


    I think this will satisfy your requirements.



    Wednesday, September 6, 2006 9:39 AM