SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE FiscalYearCalendar (
    FiscalYear INT,
    StartDate DATE,
    EndDate DATE
);
INSERT INTO FiscalYearCalendar (FiscalYear, StartDate, EndDate)
VALUES 
(2020, '2019-07-01', '2020-06-30'),
(2021, '2020-07-01', '2021-06-30'),
(2022, '2021-07-01', '2022-06-30'),
(2023, '2022-07-01', '2023-06-30'),
(2024, '2023-07-01', '2024-06-30'),
(2025, '2024-07-01', '2025-06-30'),
(2026, '2025-07-01', '2026-06-30'),
(2027, '2026-07-01', '2027-06-30'),
(2028, '2027-07-01', '2028-06-30'),
(2029, '2028-07-01', '2029-06-30');
10 rows affected

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');
5 rows affected

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