How to create database records for a financial year

Posted by David A Gibson on Stack Overflow See other posts from Stack Overflow or by David A Gibson
Published on 2010-01-26T09:38:45Z Indexed on 2010/04/25 5:13 UTC
Read the original article Hit count: 359

Filed under:

fiscal-year

|

dates

|

database

Hello,

I need to create entities (specifically contracts) in a database table that are associated with a financial year. These contracts will be applied to projects. Any contract variation will be recorded by creating a new contract record for the same financial year but the original will remain associated with the project as a history. The projects can last several years and so at any one time a project will have a live contract record for each year as well as any number of historic contracts for that year.

All of which is incidental but I'm trying to provide some context. If the contracts where for the year - it would be easy and I'd just store the Year either as a date field with the 1st of January or just an Integer. However the contracts run for financial years and I don't know how to approach this. I don't want a separate table containing the financial years as I don't want the users to have to maintain this. I don't want to store the financial year as a string "2009/2010" as this is not ideal for sorting/extracting the data.

Any ideas will be helpful, my best so far is to have starting and ending year in 2 columns and just "KNOW" that starting is April of the year etc

Thanks

© Stack Overflow or respective owner

Related posts about fiscal-year

Obtaining Current Fiscal Year from Hierarchy with MDX

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm building a report in Reporting Services 2005 based on a SSAS 2005 cube. The basic idea of the report is that they want to see sales for this fiscal year to date vs. last year sales year to date. It sounds simple, but being that this is my first "real" report based on SSAS, I'm having a hell… >>> More
Fiscal year handling strategies in database design

as seen on Stack Overflow - Search for 'Stack Overflow'
By fiscal year I mean all the data in the database (in all tables) that occurred in the particular year. Lets say that we are building an application that allows user to choose from different years. What way of implementing this would you prefer, and why: Separate fiscal year data based on multiple… >>> More
Round date to fiscal year

as seen on Stack Overflow - Search for 'Stack Overflow'
The following database view rounds the date back to the closest fiscal year (April 1st): CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS SELECT CASE WHEN to_number(to_char( SYSDATE, 'MM' )) < 4 THEN to_date('1-APR-'||to_char(add_months(SYSDATE, -12), 'YYYY'), 'dd-MON-yyyy') ELSE … >>> More
Time zone for Force.com API fiscal date literals?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a question about fiscal date literals in the Force.com API (http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_dateformats.htm): For which time zone are date ranges calculated? For example, suppose we execute the query: SELECT Id FROM Opportunity WHERE CloseDate… >>> More
How to create database records for a financial year

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, I need to create entities (specifically contracts) in a database table that are associated with a financial year. These contracts will be applied to projects. Any contract variation will be recorded by creating a new contract record for the same financial year but the original will remain… >>> More

Related posts about dates

Highlighting dates between two selected dates jQuery UI Datepicker

as seen on Stack Overflow - Search for 'Stack Overflow'
I have one datepicker with numberOfMonths set to 2. Arrival Date and Departure Date are determined using this logic (within onSelect): if ((count % 2)==0) { depart = $("#datepicker-1").datepicker('getDate'); if (arriv depart) { temp=arriv; arriv=depart; depart=temp; } $("#check-in")… >>> More
Algorithm to generate dates for business days given the range of dates

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to be able to calculate the dates of business days (mon-fri) given two dates d1 and d2. However in some locales the business days are sat-wed or sun-thur. Is there a facility in STL or C++ in general that allows for such a calculation? >>> More
Algorithim to generate dates for business days given the range of dates

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to be able to calculate the dates of business days (mon-fri) given two dates d1 and d2. However in some locales the business days are sat-wed or sun-thur. Is there a facility in STL or C++ in general that allows for such a calculation? >>> More
LINQ - How to query a range of effective dates that only has start dates

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm using C# 3.5 and EntityFramework. I have a list of items in the database that contain interest rates. Unfortunately this list only contains the Effective Start Date. I need to query this list for all items within a range. However, I can't see a way to do this without querying the database… >>> More
Frameworks to manage dates (effective date and expiry dates)

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, We have an object that can has an effective date and expiry date.(Ex. i want to maintain the price of a commodity for a time period) Business Rules - Effective date is always a valid date (a datestamp) but, expiry date can be null to indicate that the object is active throughout. Also,… >>> More