Question 115059 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get financial year wise periods for a given date range

My financial year start from 01-Jul to 30-Jun every year.
I want to find out all financial year wise periods for a given date range.

Let’s say, The date range is From_Date:16-Jun-2021 To_Date 31-Aug-2022. Then my output should be like

Start_Date, End_date

16-Jun-2021, 30-Jun-2021

01-Jul-2021, 30-Jun-2022

01-jul-2022, 31-Aug-2022

Please help me query. First record Start_Date must start from From_Date and Last record End_Date must end at To_Date

Tags:

2

Answers


  1. 0

    You want to create multiple records from one record (your date range). To accomplish this, you will need some kind of helper table.

    In this example I created that helper table using GENERATE_SERIES and use it to join it to your date range, with some logic to get the dates you want.

    dbfiddle

    --Generate a range of fiscal years
WITH FISCAL_YEARS AS (
   SELECT 
      CONCAT(SEQUENCE.YEAR, '-07-01')::DATE AS FISCAL_START, 
      CONCAT(SEQUENCE.YEAR + 1, '-06-30')::DATE AS FISCAL_END
   FROM GENERATE_SERIES(2000, 2030) AS SEQUENCE (YEAR)
), 
--Your date range
DATE_RANGE AS (
   SELECT 
      '2021-06-16'::DATE AS RANGE_START, 
      '2022-08-31'::DATE AS RANGE_END
)
SELECT 
  --Case statement in case the range_start is later 
  --than the start of the fiscal year
  CASE 
    WHEN RANGE_START > FISCAL_START 
    THEN RANGE_START 
    ELSE FISCAL_START 
  END AS START_DATE, 
  --Case statement in case the range_end is earlier
  --than the end of the fiscal year
  CASE 
    WHEN RANGE_END < FISCAL_END 
    THEN RANGE_END 
    ELSE FISCAL_END
  END AS END_DATE
FROM FISCAL_YEARS
JOIN DATE_RANGE
  --Join to get all relevant fiscal years 
  ON FISCAL_YEARS.FISCAL_START BETWEEN DATE_RANGE.RANGE_START AND DATE_RANGE.RANGE_END
  OR FISCAL_YEARS.FISCAL_END BETWEEN DATE_RANGE.RANGE_START AND DATE_RANGE.RANGE_END
    Login or Signup to reply.
  2. 0

    This should work for the current century.

    with t(fys, fye) as
(
 select (y + interval '6 months')::date,
        (y + interval '1 year 6 months - 1 day')::date
 from generate_series ('2000-01-01'::date, '2100-01-01', interval '1 year') y
),
periods (period_start, period_end) as
(
 select
  case when fys < '16-Jun-2021'::date then '16-Jun-2021'::date else fys end,
  case when fye > '31-Aug-2022'::date then '31-Aug-2022'::date else fye end
 from t
)
select * from periods where period_start < period_end;
    period_start period_end
    2021-06-16 2021-06-30
    2021-07-01 2022-06-30
    2022-07-01 2022-08-31

    Looks well as a parameterized query too with '16-Jun-2021' and '31-Aug-2022' replaced by parameter placeholders.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search