似乎你想要枚举每个里程碑的季度...

Seems you want to enumerate quarters for each milestone...

DECLARE @tblMilestones TABLE (
	[MilestoneId] [int] IDENTITY(1,1) NOT NULL,	
	[Milestone] [nvarchar](100) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL
)
INSERT INTO @tblMilestones( Milestone, StartDate, EndDate)
VALUES ('Milestone 1',	'2016-01-01 00:00:00.000',	'2016-12-31 00:00:00.000'),
('Milestone 2',	'2016-04-01 00:00:00.000',	'2016-06-30 00:00:00.000'),
('Milestone 3',	'2016-07-01 00:00:00.000',	'2016-09-30 00:00:00.000')


;WITH CTE AS
(
	--initial part
	SELECT A.MilestoneId, A.StartDate, A.EndDate, 0 AS CurrentStep,  CONVERT(VARCHAR(50), DATEPART(QUARTER, A.StartDate)) AS Quarter
	FROM @tblMilestones AS A
	--recursive part
	UNION ALL
	SELECT A.MilestoneId, A.StartDate, A.EndDate, A.CurrentStep + 3 AS CurrentStep, CONVERT(VARCHAR(50), DATEPART(QUARTER, DATEADD(MM, A.CurrentStep +3, A.StartDate))) AS Quarter
	FROM CTE AS A
	WHERE DATEADD(MM, A.CurrentStep +3 ,A.StartDate) < DATEADD(DD, 1, A.EndDate)
)
SELECT DISTINCT b.MilestoneId, b.StartDate, b.EndDate, STUFF((SELECT c.Quarter + ','
                        FROM CTE AS c
						WHERE c.MilestoneId = b.MilestoneId 
						ORDER BY c.CurrentStep 
                        FOR XML PATH('')), 2, 0,'') AS Quarters
FROM CTE AS b

结果：

Result:

MilestoneId	StartDate		EndDate			Quarters
------------------------------------------------------------------------
1		2016-01-01 00:00:00.000	2016-12-31 00:00:00.000	1,2,3,4,
2		2016-04-01 00:00:00.000	2016-06-30 00:00:00.000	2,
3		2016-07-01 00:00:00.000	2016-09-30 00:00:00.000	3,

请注意...... 公用表表达式 [ ^ ]规则！

Note that... Common Table Expressions[^] rules!

如果我读得正确，你就是寻找与指定日期范围重叠的所有里程碑。这样的事情可以解决这个问题：

If I'm reading that correctly, you're looking for all the milestones which overlap a specified date range. Something like this should do the trick:

DECLARE @QuarterStartDate date = '20160101';
DECLARE @QuarterEndDate date = '20160331';

SELECT
    MilestoneId,
    Milestone,
    StartDate,
    EndDate
FROM
    tblMilestones
WHERE
    StartDate <= @QuarterEndDate
And
    EndDate >= @QuarterStartDate
;