Getting the fiscal years from start and end dates in SQL

Question

My goal is get the fiscal years clients were served in, even if the program start and end dates span multiple fiscal years. If they span multiple fiscal years, even if by 1 day, then I want to see a row for each fiscal year. I also need to account for the case when a client has a program start date but no end date...in that situation I would need it to output the start date's fiscal year only. I am not sure my code is capturing all the scenarios. I have worked through other iterations of this code as well, but I can't get the expected outcome. I am using MS Sql Server.

SELECT DISTINCT cp.document_id AS Client_ProfileID
,CASE
    WHEN (MONTH(pe.ServiceStartDate) >= 7 OR (MONTH(pe.ServiceStartDate) = 6 AND DAY(pe.ServiceStartDate) = 30)) 
        AND (MONTH(pe.ServiceEndDate) >= 7 OR (MONTH(pe.ServiceEndDate) = 6 AND DAY(pe.ServiceEndDate) = 30)) THEN YEAR(pe.ServiceStartDate)
    WHEN (MONTH(pe.ServiceStartDate) < 7 OR (MONTH(pe.ServiceStartDate) = 6 AND DAY(pe.ServiceStartDate) = 30)) 
        AND (MONTH(pe.ServiceEndDate) >= 7 OR (MONTH(pe.ServiceEndDate) = 6 AND DAY(pe.ServiceEndDate) = 30)) THEN YEAR(pe.ServiceEndDate) - 1
    ELSE YEAR(pe.ServiceEndDate)
END AS fiscal_year

--Removed Joins--

GROUP BY cp.document_id
,CASE
    WHEN (MONTH(pe.ServiceStartDate) >= 7 OR (MONTH(pe.ServiceStartDate) = 6 AND DAY(pe.ServiceStartDate) = 30)) 
        AND (MONTH(pe.ServiceEndDate) >= 7 OR (MONTH(pe.ServiceEndDate) = 6 AND DAY(pe.ServiceEndDate) = 30)) THEN YEAR(pe.ServiceStartDate)
    WHEN (MONTH(pe.ServiceStartDate) < 7 OR (MONTH(pe.ServiceStartDate) = 6 AND DAY(pe.ServiceStartDate) = 30)) 
        AND (MONTH(pe.ServiceEndDate) >= 7 OR (MONTH(pe.ServiceEndDate) = 6 AND DAY(pe.ServiceEndDate) = 30)) THEN YEAR(pe.ServiceEndDate) - 1
    ELSE YEAR(pe.ServiceEndDate)
END

GO

Here's an example output of what I would expect to see if a client start date was June 30, 2022 and the end date was July 1, 2022:

Client_ProfileID	fiscal_year
1	2022
1	2023

Answer 1

Construct a fiscal year calendar and populate it e.g:

CREATE TABLE FiscalYearCalendar (
    FiscalYear INT,
    StartDate DATE,
    EndDate DATE
);

INSERT INTO FiscalYearCalendar (FiscalYear, StartDate, EndDate)
VALUES 
(2023, '2022-07-01', '2023-06-30'),
(2024, '2023-07-01', '2024-06-30');

Now some sample data to demo how the fiscal calendar is used:

CREATE TABLE ServiceRecords (
    document_id INT,
    ServiceStartDate DATE,
    ServiceEndDate DATE
);

INSERT INTO ServiceRecords (document_id, ServiceStartDate, ServiceEndDate)
VALUES 
(1, '2023-01-01', '2023-02-21'),
(2, '2023-02-02', '2023-03-22'),
(3, '2023-03-03', '2023-04-23'),
(4, '2023-06-04', '2023-07-24'),
(5, '2023-07-05', '2023-07-25');

Combine the service records and the fiscal calendar, this will allow 1 service record to span multiple fiscal years and hence return multiple rows (see Client_ProfileID 4):

SELECT
      sr.document_id AS Client_ProfileID
    , fyc.FiscalYear
FROM ServiceRecords sr
JOIN FiscalYearCalendar fyc ON 
  (sr.ServiceStartDate <= fyc.EndDate AND sr.ServiceEndDate >= fyc.StartDate)

Client_ProfileID	FiscalYear
1	2023
2	2023
3	2023
4	2023
4	2024
5	2024

fiddle

Note the fiscal calendar could be temporary (e.g. a cte) but it is likely to be useful in many situations so I suggest keeping it as a table.

Answer 2

Assuming you are using SQLServer (because you tagged SSMS) we can build a calendar dynamically using your date limits; and then use that:

with OPData as (
select *
from (values
     (1, '2022-06-30', '2022-07-01')
    ,(2, '2017-04-05', '2019-12-31')
    ) T(Client_ProfileID, StartDate, EndDate)
),
 FCalendar as (
    select 
          StartFrom=min(StartDate)
        , EndHere=max(EndDate)
        , FY=year(dateadd(month,6, min(StartDate)))
        , FYStart=DATEFROMPARTS(year(dateadd(month,6, min(StartDate)))-1, 7, 1)
        , FYEnd=DATEFROMPARTS(year(dateadd(month,6, min(StartDate))), 6, 30)
    from OPData
    union all
    select 
          FC.StartFrom
        , FC.EndHere
        , FY=FC.FY+1
        , FYStart=dateadd(year,1, FC.FYStart)
        , FYEnd=dateadd(year,1,FC.FYEnd)
    from FCalendar FC
    where FC.FYStart < FC.EndHere
)
select od.Client_ProfileID
from OPData od
     inner join
     FCalendar FC
     on OD.StartDate <= FC.FYEnd
     and OD.EndDate >= FC.FYStart

The first CTE (OpData) simulates your data; then we use a recursive query to find all years between your min-start and max-end dates. By default, this will only generate max 100 rows (if you need more than 100 years check MAXRECURSION query option). We then use the standard conditions for overlapping time-periods to find which financial years to be returned.

Note:Adding 6 months to a calendar date gives us a date whose year will be the fiscal year; provided that your fiscal years run from 1-jul-prevyyyy to 30-jun-yyyy; where yyyy is the fiscal year.