if exists (select * from tempdb.sys.objects where type = 'U' and name like '#DatePeriod%')
	drop table #DatePeriod

Create table #DatePeriod
	(StartDate DateTime,
	EndDate DateTime,
	Units int)
-- create the sample data
INSERT INTO #DatePeriod
	SELECT '20080307',	'20080416',	3 UNION ALL
	SELECT '20080417',	'20080430',	5 UNION ALL
	SELECT '20080501',	'20080629',	1 UNION ALL
	SELECT '20080630',	'20080714',	4 UNION ALL
	SELECT '20080715',	'20080902',	1 UNION ALL
	SELECT '20080903',	'20080930',	8 UNION ALL
	SELECT '20081001',	'20081022',	3

SELECT * FROM #DatePeriod

DROP TABLE #DatePeriod
-- show the final result I need
SELECT '20080307' as 'begin',	'20080331' as 'end',	3 as 'Units' UNION ALL
SELECT '20080401',	'20080416',	3 UNION ALL
SELECT '20080417',	'20080430',	5 UNION ALL
SELECT '20080501',	'20080531',	1 UNION ALL
SELECT '20080601',	'20080629',	1 UNION ALL
SELECT '20080630',	'20080630',	4 UNION ALL
SELECT '20080701',	'20080714',	4 UNION ALL
SELECT '20080715',	'20080731',	1 UNION ALL
SELECT '20080801',	'20080831',	1 UNION ALL
SELECT '20080901',	'20080902',	1 UNION ALL
SELECT '20080903',	'20080930',	8 UNION ALL
SELECT '20081001',	'20081022',	3

if object_id('tempdb..#Calendar') is not null
        drop table #Calendar

Create table #Calendar(dtStart DateTime,dtEnd as dateadd(day,-1,dateadd(month,1,dtStart)))
insert into #Calendar(dtStart)
select '20080301'  union all
select '20080401'  union all 
select '20080501'   union all
select '20080601'   union all
select '20080701'   union all
select '20080801'   union all
select '20080901'   union all
select '20081001' 


SELECT CASE WHEN p.StartDate>c.dtStart THEN p.StartDate ELSE c.dtStart END AS 'begin',
       CASE WHEN p.EndDate<c.dtEnd THEN p.EndDate ELSE c.dtEnd END AS 'end',
       p.Units
FROM #DatePeriod p
INNER JOIN #Calendar c ON c.dtStart BETWEEN p.StartDate AND p.EndDate OR c.dtEnd BETWEEN p.StartDate AND p.EndDate
ORDER BY p.StartDate 

if exists (select * from tempdb..sysobjects where type = 'U' and name like '#Tally%')
        drop table #Tally

Create table #Tally
        (Numbers tinyint)
-- fill the tally-table with numeric range
INSERT INTO #Tally
        SELECT 0 UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4


if exists (select * from tempdb..sysobjects where type = 'U' and name like '#DatePeriod%')
        drop table #DatePeriod

Create table #DatePeriod
        (StartDate DateTime,
        EndDate DateTime,
        Units int)
-- create the sample data
INSERT INTO #DatePeriod
        SELECT '20080307',        '20080416',        3 UNION ALL
        SELECT '20080417',        '20080430',        5 UNION ALL
        SELECT '20080501',        '20080629',        1 UNION ALL
        SELECT '20080630',        '20080714',        4 UNION ALL
        SELECT '20080715',        '20080902',        1 UNION ALL
        SELECT '20080903',        '20080930',        8 UNION ALL
        SELECT '20081001',        '20081022',        3


-- select and calculate the periods
SELECT
	StartDate AS OriginalStartDate
	, EndDate AS OriginalEndDate
	, CASE WHEN (DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers ,0)) <= StartDate
		THEN StartDate
		ELSE DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers ,0)
	END AS PeriodStart

	, CASE WHEN (DateAdd(Day, -1, DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers + 1 ,0))) > EndDate
		THEN EndDate
		ELSE DateAdd(Day, -1, DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers + 1 ,0))
	END AS PeriodEnd
	, Units
FROM
	#DatePeriod, #Tally
WHERE
	datediff(m, StartDate, EndDate) >= Numbers
ORDER BY StartDate, Numbers


DROP TABLE #DatePeriod
DROP TABLE #Tally