How to split row with date range into multip rows by year

Question

The result of my simple SQL query

SELECT ID, STARDATE, ENDDATE
FROM A
INNER JOIN B ON A.ID = B.ID
    AND B.CONTRACTID = 572786
WHERE A.ISACTIVE = 1

is:

ID	STARDATE	ENDDATE
394539	2024-03-01	2025-12-31
394540	2026-01-01	2026-12-31

But now I would like to convert/split the result into three rows by year (desired result):

YEAR	ID	STARTDATE	ENDDATE
2024	394539	2024-03-01	2024-12-31
2025	394539	2025-01-01	2025-12-31
2026	394540	2026-01-01	2026-12-31

Is there anyone who can explain me how to tackle this in SQL?

Tried to find a solution on stackoverflow but I cant find any answers.

Answer 1

We can solve your problem with the help of a calendar table. In this case, the calendar table can maintain one record with start and end dates for each year which you want to appear in the report.

STARDATE   | ENDDATE
2024-01-01 | 2024-12-31
2025-01-01 | 2025-12-31
2026-01-01 | 2026-12-31

Here is the query you may use:

SELECT YEAR(C.STARDATE) AS YEAR, A.ID, A.STARDATE, A.ENDDATE
FROM CALENDAR C
LEFT JOIN A
    ON A.STARDATE <= C.ENDDATE AND A.ENDDATE >= C.STARDATE
INNER JOIN B
    ON B.ID = A.ID
WHERE
    A.ISACTIVE = 1 AND
    B.CONTRACTID = 572786;

Note that depending on your database, it might not have a YEAR() function to extract the year from a date. The syntax might have to change in the first line of my query.