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 |