Hi Anzio,

I have work out your solution. Maybe others can benefit from this code.

As you allready mentioned, this solution is more generic. The TALLY table now only contains the number of months (between startdate and enddate) instead of a fixed startdate and enddate of possible periods. A bit more coding is involved to get the dates, but that is done only once. 😉

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