How do i display milestone-1 in 4(q1,q2,q3,q4) indivisual quarter

Question

0.00/5 (No votes)

See more:

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.

SQL

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:

SQL

SELECT * FROM tblMilestones
   WHERE  (StartDate BETWEEN '04/01/2016' AND '06/30/2016' 
   OR EndDate>= '04/01/2016' AND EndDate<='06/30/2016')

Posted 8-Mar-16 22:11pm

bubai banerjee

Updated 9-Mar-16 8:09am

Maciej Los

v3

Add a Solution

Comments

Herman<T>.Instance 9-Mar-16 5:04am

Which tables in your schema are used for this prblem. Do you have a Table called yearlyQuarters with the four Quarters and their start and end date an quarterlyname fields?
In that case you can match on table asking the Quarterlyname field or something like that

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-03-09T08:09:00

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

SQL

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:

C#

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!

Richard Deeming · Answer 2 · 2016-03-09T01:01:00

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:

SQL

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

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

How do i display milestone-1 in 4(q1,q2,q3,q4) indivisual quarter

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0