Click here to Skip to main content
14,695,660 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Table t1
ID	BegDate	EndDate
1	01-01-2020	01-07-2021
2	01-01-2020	01-02-2021
3	01-01-2020	01-12-2021


I need to split table t1 records into month wise in SQL Server 12 Below is the example for ID=1


ID	OLD_ID	BegDate	EndDate
1	1	01-01-2020	31-01-2020
2	1	01-02-2020	29-02-2020
3	1	01-03-2020	31-03-2020
4	1	01-04-2020	30-04-2020
5	1	01-05-2020	31-05-2020
6	1	01-06-2020	30-06-2020
7	1	01-07-2020	31-07-2020
8	1	01-08-2020	31-08-2020
9	1	01-09-2020	30-09-2020
10	1	01-10-2020	31-10-2020
11	1	01-11-2020	30-11-2020
12	1	01-12-2020	31-12-2020
13	1	01-01-2021	31-01-2021
14	1	01-02-2021	20-02-2021
15	1	01-03-2021	31-03-2021
16	1	01-04-2021	30-04-2021
17	1	01-05-2021	31-05-2021
18	1	01-06-2021	30-06-2021


What I have tried:

I used Cursor but unable to get required result
Posted
Updated 7-Jun-20 22:00pm
Comments
Patrice T 6-Jun-20 1:18am
   
And you plan to show your code ?

1 solution

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
(
	--initial part
	SELECT ID, BegDate AS StartOfMonth, DATEADD(DD, -1, DATEADD(MM, 1, BegDate)) AS EndOfMonth, EndDate
	FROM @dateranges
	-- recursive part
	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[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900