sql - Split date range into months (Using CTE)

Question

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_date DATE);
INSERT @d VALUES (7,'2013-11-05','2013-11-25'),(8,'2013-07-05','2013-12-25'),(7,'2013-02-07','2013-04-14');
WITH cte AS
(SELECT value,from_date
      , to_date
      , from_date AS mo_from_date
      , DATEADD(day, day(from_date)* -1 + 1, from_date) AS bom_date
   FROM @d
UNION ALL
SELECT value, from_date
     , to_date
     , DATEADD(month,1,bom_date)
     , DATEADD(month,1,bom_date)
  FROM cte
 where DATEADD(month,1,mo_from_date) < to_date
)
SELECT value,mo_from_date
     , CASE when to_date < DATEADD(month,1,bom_date) THEN
           to_date
       ELSE
           DATEADD(day, -1, DATEADD(month,1,bom_date))
       END AS mo_to_date
  FROM cte

output:

value   mo_from_date    mo_to_date
7   2013-11-05  2013-11-25
8   2013-07-05  2013-07-31
8   2013-08-01  2013-08-31
8   2013-09-01  2013-09-30
8   2013-10-01  2013-10-31
8   2013-11-01  2013-11-30
8   2013-12-01  2013-12-25
9   2013-02-07  2013-02-28
10  2013-03-01  2013-03-31
11  2013-04-01  2013-04-14

I have tried below query but its not working,can anybody update this query

;with MonthList as (
    select 
        DATEADD(month, M, '12/1/1899') as 'FirstDay',
        dateadd(day, -1, dateadd(month, M + 1, '12/1/1899')) as 'LastDay',
        DATEADD(month, M + 1, '12/1/1899') as 'FirstDayNextMonth'
    from (
        select top 3000 ROW_NUMBER() over (order by s.name) as 'M'
        from master..spt_values s) s
)

select
   t.value, ml.FirstDay as from_date, ml.LastDay as to_date
from
    @d t
    inner join MonthList ml
        on  t.from_date < ml.FirstDayNextMonth
            and t.to_date >= ml.FirstDay

Answer 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?