You can use CTE:
SET DATEFORMAT dmy;
DECLARE @dateranges TABLE(ID INT, BegDate DATE, EndDate DATE)
INSERT INTO @dateranges(ID, BegDate, EndDate)
VALUES(1, '01-01-2020', '01-07-2021'),
(2, '01-01-2020', '01-02-2021'),
(3, '01-01-2020', '01-12-2021')
;WITH CTE AS
(
SELECT ID, BegDate AS StartOfMonth, DATEADD(DD, -1, DATEADD(MM, 1, BegDate)) AS EndOfMonth, EndDate
FROM @dateranges
UNION ALL
SELECT ID, DATEADD(MM, 1, StartOfMonth) AS StartOfMonth, DATEADD(DD, -1, DATEADD(MM, 2, StartOfMonth)) AS EndOfMonth, EndDate
FROM CTE
WHERE DATEADD(MM, 1, StartOfMonth)< EndDate
)
SELECT ID, StartOfMonth, EndOfMonth
FROM CTE
WHERE ID = 1
For further details, please see:
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[
^]
Common Table Expressions (Introduction to CTE's) - Essential SQL[
^]
Mastering Common Table Expression or CTE in SQL Server[
^]
Submit an article or tip