SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT * INTO #t  FROM (    --   DROP TABLE IF EXISTS  #t
              SELECT 100 ID,  CAST('2022-03-02' AS DATE) eStart ,  CAST('2022-03-15' AS DATE) eEnd, '1 Same Month island' note  
        UNION SELECT 200,  '2022-03-01' ,   '2022-03-27', '2 Same Month Start'
        UNION SELECT 300,  '2022-03-08' ,   '2022-03-31', '3 Same Month End' 
    
        UNION SELECT 440,  '2022-01-15' ,   '2022-02-28', '4 Diff Month End'  
        UNION SELECT 550,  '2022-03-08' ,   '2022-05-10', '5 Diff Month Island'  
        UNION SELECT 660,  '2022-03-1'  ,   '2022-6-15',  '6 Diff Month Start' 
    ) b      --    SELECT * FROM #t
  
6 rows affected

-- recursive query way
with rcte as
(
  select ID, 
         eStart, 
         eEnd = case when eEnd < eomonth(eStart) 
                     then eEnd 
                     else eomonth(eStart) 
                     end, 
         fEnd = eEnd,
         note
  from   #t

  union all

  select t.ID, 
         eStart = dateadd(day, 1, r.eEnd),
         eEnd   = case when fEnd < eomonth(dateadd(day, 1, r.eEnd)) 
                       then fEnd 
                       else eomonth(dateadd(day, 1, r.eEnd)) 
                       end,
         fEnd,
         r.note
  from   #t t
         inner join rcte r on t.ID = r.ID
  where  r.fEnd > r.eEnd
)
select ID, eStart, eEnd, note
from   rcte
order by ID, eStart


ID
eStart
eEnd
note


100
2022-03-02
2022-03-15
1 Same Month island

200
2022-03-01
2022-03-27
2 Same Month Start

300
2022-03-08
2022-03-31
3 Same Month End

440
2022-01-15
2022-01-31
4 Diff Month End

440
2022-02-01
2022-02-28
4 Diff Month End

550
2022-03-08
2022-03-31
5 Diff Month Island

550
2022-04-01
2022-04-30
5 Diff Month Island

550
2022-05-01
2022-05-10
5 Diff Month Island

660
2022-03-01
2022-03-31
6 Diff Month Start

660
2022-04-01
2022-04-30
6 Diff Month Start

660
2022-05-01
2022-05-31
6 Diff Month Start

660
2022-06-01
2022-06-15
6 Diff Month Start


-- tally table way
with numbers as  -- using recursive cte to create a number table
(
  select n = 0
  union all
  select n = n + 1
  from   numbers
  where  n < 99
)
select t.ID,
       eStart = case when n = 0 
                     then t.eStart 
                     else convert(date, dateadd(month, datediff(month, 0, t.eStart) + n, 0))
                     end,
       eEnd    = case when n = datediff(month, eStart, eEnd)
                     then t.eEnd
                     else eomonth(dateadd(month, datediff(month, 0, t.eStart) + n, 0))
                     end,
       note
from   #t t
       inner join numbers n on n.n <= datediff(month, eStart, eEnd)
order by t.ID, eStart


ID
eStart
eEnd
note


100
2022-03-02
2022-03-15
1 Same Month island

200
2022-03-01
2022-03-27
2 Same Month Start

300
2022-03-08
2022-03-31
3 Same Month End

440
2022-01-15
2022-01-31
4 Diff Month End

440
2022-02-01
2022-02-28
4 Diff Month End

550
2022-03-08
2022-03-31
5 Diff Month Island

550
2022-04-01
2022-04-30
5 Diff Month Island

550
2022-05-01
2022-05-10
5 Diff Month Island

660
2022-03-01
2022-03-31
6 Diff Month Start

660
2022-04-01
2022-04-30
6 Diff Month Start

660
2022-05-01
2022-05-31
6 Diff Month Start

660
2022-06-01
2022-06-15
6 Diff Month Start

ID	eStart	eEnd	note
100	2022-03-02	2022-03-15	1 Same Month island
200	2022-03-01	2022-03-27	2 Same Month Start
300	2022-03-08	2022-03-31	3 Same Month End
440	2022-01-15	2022-01-31	4 Diff Month End
440	2022-02-01	2022-02-28	4 Diff Month End
550	2022-03-08	2022-03-31	5 Diff Month Island
550	2022-04-01	2022-04-30	5 Diff Month Island
550	2022-05-01	2022-05-10	5 Diff Month Island
660	2022-03-01	2022-03-31	6 Diff Month Start
660	2022-04-01	2022-04-30	6 Diff Month Start
660	2022-05-01	2022-05-31	6 Diff Month Start
660	2022-06-01	2022-06-15	6 Diff Month Start