Hi,
Given a From Date, To Date, Fiscal Year system. I want to get all the split-up duration within the given duration based on the breakup asked for.
Breakups are:
1) Weekly: This should be followed from Monday through Sunday.
2) Monthly: This should be 1st of a month through end of the month.
3) Quarterly
Since the Fiscal Year system is Apr-Mar, quarterly breakups should be Apr-Jun, Jul-Sep, Oct-Dec, Jan-Mar
4) Half-yearly:
Since the Fiscal Year system is Apr-Mar, half-yearly breakups should be Apr-Sep, Oct-Mar.
5) Annual:
Since the Fiscal Year system is Apr-Mar, annual breakup should be Apr-Mar.
Explained below with examples.
Example 1:
From Date: Feb-10-2010
To Date: Feb-10-2010
Fiscal Year system: Apr to Mar
Weekly Breakup: Feb-08-2010 to Feb-14-2010
Monthly Breakup: Feb-01-2010 to Feb-28-2010
Quarterly Breakup: Jan-01-2010 to Mar-31-2010
Half-yearly Breakup: Oct-01-2009 to Mar-31-2010
Annual Breakup: Apr-01-2009 to Mar-31-2010
Am looking for approach/algorithm to solve this in PostgreSQL 8.2.
This post is similar to a question posted here: http://stackoverflow.com/questions/2167755/date-split-up-based-on-fiscal-year