till how many months you've to repeat this?DECLARE @date1 DATETIME = '20120101'DECLARE @date2 DATETIME = '20120430';;With Months (N)AS(SELECT 0UNION ALLSELECT N+1FROM MonthsWHERE DATEADD(mm,N+1,@date1) <=@date2)SELECT a.ID, a.locID, DATEADD(mm,N,@date1) AS OperatingDateFROM dataset ACROSS JOIN Months mWHERE A.startDate >= @date1 AND (A.endDate <= @date1 OR a.endDate IS NULL) AND A.condition <> 1
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/