Getting the fiscal years from start and end dates in SQL

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

Solution

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.