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
Advice needed for modeling the financial year.
Rhubarb
The solution is the use of the user-define data type;
Ex: you can create a user-define data type by using:
DECLAREfinancialYear(@ValueIDnvarchar(15), @KPIIDnvarchar(15), @Budgetnvarchar(15), @Actualnvarchar(15), @Stretchnvarchar(15), @Date(15) )CONSTRAINT CheckfinancialYearCHECK (financialYearLIKE '[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.