Dynamic fiscal year query

Dynamic fiscal year query

I have several years (and growing) of data that I need to query whose results need to be grouped by our fiscal years, Oct to Sep.

So I don't have to create a new sub-query and edit the primary query every year, I would like to have a dynamic moving window of time that goes back 5 fiscal years or so, 1 sub query per fiscal year counting back from the current one.

As an example a sub-query for the last fiscal year would be something like:
  1. Sub-Query name: "FY-1"

    SELECT
      "Staff_ID",
      "Amount"

    FROM "Staff_Salary"
    WHERE Salary_Start_Date" = [?? CURRENT FISCAL YEAR -1 ??]
The primary query would be something like:
  1. SELECT
  2.   t1."Staff_Name" "Name",
  3.   t2."Amount" "Curent FY Amount",
  4.   t3."Amount" "Previous FY Amount"

  6. FROM "Staff" t1
  7. LEFT JOIN "FY_Curr" t2 ON t2.Staff_ID = t1.Staff_ID
  8. LEFT JOIN "FY-1" t3 ON t3.Staff_ID = t1.Staff_ID

Again, I'm trying to do this so I never have to edit it every year.

Thanks,

For you help