Separating dates based on fiscal year

Question

Given the date from the date to the date and the system of the fiscal year, I want to get the entire duration of the gap within and taking into account the date based on the system of the fiscal year. The following are examples:

Example 1: Fiscal Year System: April to March

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

Based on the fiscal year system, the duration should be divided into:

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

Example 2: Fiscal Year System: April to March

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

Based on the fiscal year system, the duration should be divided into:

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

We are looking for an approach / algorithm to solve this problem in PostgreSQL 8.2.

Hi,

Gnanam

Answer 1

I really use the Andomar solution (with the addition of processes that automatically populate the Periods table), but for fun here is a solution that does not 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; 

Answer 2

You can create a table containing the beginning and end of all fiscal years, fe

 Periods (PeriodStartDt, PeriodEndDt) 

You can then join tables together if they are at least partially overlapping. Use the case to select the end of a period or the end of a line, whichever is later. For example (not verified):

 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 3

 CREATE TABLE your_table (start_date date, end_date date); INSERT INTO your_table VALUES (CONVERT (date, GETDATE()),CONVERT (date, DATEADD(year, -1, GETDATE())) ); WITH mycte AS ( SELECT 1 as id UNION ALL SELECT id + 1 FROM mycte WHERE id + 1 < = 12 ), cte_distribution as ( SELECT *, DATEPART (month,DATEADD(month, mycte.id - 1, your_table.start_date)) as month_number , DATEPART (YEAR,DATEADD(month, mycte.id - 1, your_table.start_date)) as cal_year, 12000/12 as cash FROM your_table CROSS JOIN mycte ) select *, (CASE WHEN month_number between 1 and 3 THEN '1st quarter' WHEN month_number between 4 and 6 THEN '2nd quarter' WHEN month_number between 7 and 9 THEN '3rd quarter' WHEN month_number between 9 and 12 THEN '4th quarter' END) as Quarter, CASE WHEN month_number between 1 and 6 THEN CAST(CAST((cal_year - 1) as CHAR(4)) + '-' + CAST(cal_year as CHAR(4)) AS CHAR(9)) WHEN month_number between 6 and 12 THEN CAST(CAST((cal_year) as CHAR(4)) + '-' + CAST((cal_year + 1) as CHAR(4)) AS CHAR(9)) ELSE NULL END as fin_year from cte_distribution;