Date split-up based on Fiscal Year

Question

Hi,

Given a From Date, To Date and a Fiscal Year system, I want to get all the split-up duration within the given From & To Date based on the Fiscal Year system. Explained below with examples:

Example 1: Fiscal Year system: Apr to Mar

From Date: Jan-05-2008 To Date: May-15-2008

Based on Fiscal Year system, duration should be splitted into:

Jan-05-2008 to Mar-31-2008
Apr-01-2008 to May-15-2008

Example 2: Fiscal Year system: Apr to Mar

From Date: Jan-17-2008 To Date: May-20-2009

Based on Fiscal Year system, duration should be splitted into:

Jan-17-2008 to Mar-31-2008
Apr-01-2008 to Mar-31-2009
Apr-01-2009 to May-20-2009

Am looking for approach/algorithm to solve this in PostgreSQL 8.2.

Regards,

Gnanam

Answer 1

You could create a table containing the start and end of all fiscal years, f.e.

Periods (PeriodStartDt, PeriodEndDt)

Then you can join the tables together if they at least partly overlap. Use a case statement to select the end of the period or the end of the row, depending on which is later. For example (not tested):

select      case when yt.StartDt < p.PeriodStartDt then p.PeriodStartDt
                 else yt.StartDt
            end as SplitStart
,           case when yt.EndDt > p.PeriodEndDt then p.PeriodEndDt
                 else yt.EndDt
            end as SplitEnd
,           yt.*
from        YourTable yt
inner join  Periods p
on          yt.StartDt < p.PeriodEndDate
            and yt.EndDt >= p.PeriodStartDate

Answer 2

I actually favor Andomar's solution (with the addition of a processes that automatically fills the Periods table), but for fun here's a solution that doesn't require it.

CREATE TABLE your_table (start_date date, end_date date);
INSERT INTO your_table VALUES ('Jan-17-2008', 'May-20-2009');

SELECT
    GREATEST(start_date, ('04-01-'||series.year)::date) AS year_start,
    LEAST(end_date, ('03-31-'||series.year + 1)::date) AS year_end
FROM
    (SELECT
        start_date,
        end_date,
        generate_series(
            date_part('year', your_table.start_date - INTERVAL '3 months')::int,
            date_part('year', your_table.end_date - INTERVAL '3 months')::int)
    FROM your_table) AS series(start_date, end_date, year)
ORDER BY
    start_date;