Recursive Query in Azure Synapse Analytics for Dates

In the table below, how to insert rows with the first and last date of years between the START_DATE and END_DATE column?

EMPID	EMPNAME	START_DATE	END_DATE
1001	Shivansh	2015-09-01	2018-03-31
1004	Mayank	2019-04-01	2020-06-30

The output should look as follows:

EMPID	EMPNAME	START_DATE	END_DATE
1001	Shivansh	2015-09-01	2015-12-31
1001	Shivansh	2016-01-01	2016-12-31
1001	Shivansh	2017-01-01	2017-12-31
1001	Shivansh	2018-01-01	2018-03-31
1004	Mayank	2019-04-01	2019-12-31
1004	Mayank	2020-01-01	2020-06-30

This has to be implemented using loops as Azure Synapse Analytics doesn't support Recursive common table expressions

https://stackoverflow.com/questions/66470237/recursive-query-in-azure-synapse-analytics-for-dates March 04, 2021 at 03:17PM