Split Date Range Into Months (Using CTE)

I have this query and I want an alternative for this query without using recursive CTE. Can Anybody please edit and update the query DECLARE @d TABLE(value int ,from_date DATE, to_

Solution 1:

This is a method using an inline tally:

DECLARE @d TABLE(value int ,from_date DATE, to_date DATE);
INSERT @d VALUES (7,'2013-11-25','2013-11-05'),(8,'2013-07-25','2013-12-05');

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (SELECT MAX(DATEDIFF(MONTH,from_date,to_date)) FROM @d)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 months, add more cross joins for more months
SELECT d.[value],
       CASE WHEN DATEADD(DAY,1,EOMONTH(DATEADD(MONTH,T.I,d.from_date),-1)) < d.from_date THEN d.from_date ELSE DATEADD(DAY,1,EOMONTH(DATEADD(MONTH,T.I,d.from_date),-1)) END AS mo_from_date,
       CASE WHEN EOMONTH(DATEADD(MONTH,T.I,d.from_date)) > d.to_date THEN d.to_date ELSE DATEADD(MONTH,T.I,d.from_date) END AS mo_to_date 
FROM @d d
     JOIN Tally T ON DATEDIFF(MONTH,from_date,to_date) >= T.I;

I do, personally, however, suggest you get yourself a Calendar Table, then the problem is far more simple:

SELECT d.[value],
       CASE WHEN d.from_date > MIN(CT.CalendarDate) THEN d.from_date ELSE MIN(CT.CalendarDate) END AS mo_from_date,
       CASE WHEN d.to_date < MAX(CT.CalendarDate) THEN d.to_date ELSE MAX(CT.CalendarDate) END AS mo_to_date
FROM @d d
     JOIN dbo.CalendarTable CT ON d.from_date <= CT.CalendarDate --To assumes that the to_date being before the
                              AND d.to_date >= CT.CalendarDate   --the from date is an error.
GROUP BY d.[value],
         d.from_date,
         d.to_date,
         MONTH(CT.CalendarDate),
         YEAR(CT.CalendarDate);

Both solutions assume that the from date should be before the to date though; even though that it appears to not be true in your data. If your from date can be after your to date, I would suggest you have data issues, as how can something start after it has ended?