如何在4(q1,q2,q3,q4)个别季度显示里程碑-1 [英] How do i display milestone-1 in 4(q1,q2,q3,q4) indivisual quarter
问题描述
我有四个季度。
如果里程碑开始日期2016年1月1日至2016年3月31日结束日期Q1
如果里程碑开始日期2016年4月1日至2016年6月30日结束,那么Q2
如果里程碑开始日期2016年7月1日至2016年12月30日至2016年9月结束日期Q3
如果里程碑开始日期2016年10月1日至2016年12月31日结束,那么Q4
如果里程碑-2开始日期2016年4月1日至2016年12月30日结束日期Q2
但是如果里程碑-1开始日期2016年1月1日和2016年12月31日结束日那么它将在Q1,Q2,Q3,Q4显示
任何人都可以帮我解决SQL查询问题。提前谢谢。
如何在4中显示里程碑-1(Q1,Q2,Q3) ,Q4)个别季度。
I have four Quarter.
if milestone Start Date 01-January-2016 and endDate 31-Mar-2016 then Q1
if milestone Start Date 01-April-2016 and endDate 30-June-2016 then Q2
if milestone Start Date 01-July-2016 and endDate 30-September-2016 then Q3
if milestone Start Date 01-October-2016 and endDate 31-December-2016 then Q4
if milestone-2 Start Date 01-April-2016 and endDate 30-06-2016 then it be in Q2
but if milestone-1 Start Date 01-January-2016 and endDate 31-Dec-2016 then it be display in Q1,Q2,Q3,Q4
Can anyone please help me with sql query.Thanks in advance.
How do i display Milestone-1 in 4(Q1,Q2,Q3,Q4) indivisual quarter.
CREATE TABLE tblMilestones(
[MilestoneId] [int] IDENTITY(1,1) NOT NULL,
[Milestone] [nvarchar](100) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
)
MilestoneId Milestone StartDate EndDate
1 Milestone 1 2016-01-01 00:00:00.000 2016-12-31 00:00:00.000
2 Milestone 2 2016-04-01 00:00:00.000 2016-06-30 00:00:00.000
6 Milestone 3 2016-07-01 00:00:00.000 2016-09-30 00:00:00.000
我尝试过:
What I have tried:
SELECT * FROM tblMilestones
WHERE (StartDate BETWEEN '04/01/2016' AND '06/30/2016'
OR EndDate>= '04/01/2016' AND EndDate<='06/30/2016')
推荐答案
似乎你想要枚举每个里程碑的季度...
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
;
这篇关于如何在4(q1,q2,q3,q4)个别季度显示里程碑-1的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!