You'll need a calendar table with month start and end dates

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

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537