Reputation: 89
This is how my table looks.I want to show only one row for each mcode. The rule here would be for AType =start Milestone we have to take MIN(StartDate) and for AType =Finish Milestone consider the max(EndDate).
PID AId Mcode AType StartDate EndDate
1 ABC1 PM105 Start Milestone 2013-08-12 00:00:00.000 NULL
1 ABC2 PM200 Start Milestone 2015-06-22 00:00:00.000 NULL
1 ABC3 PM200 Start Milestone 2014-08-25 00:00:00.000 NULL
1 ABC4 PM200 Start Milestone 2014-09-29 00:00:00.000 NULL
1 ABC5 PM200 Start Milestone 2014-08-11 00:00:00.000 NULL
1 ABC6 PM200 Start Milestone 2014-08-11 00:00:00.000 NULL
1 ABC7 PM235 Finish Milestone NULL 2015-11-10 00:00:00.000
1 ABC8 PM235 Finish Milestone NULL 2015-11-18 00:00:00.000
1 ABC9 PM235 Finish Milestone NULL 2015-11-10 00:00:00.000
1 ABC10 PM235 Finish Milestone NULL 2015-09-03 00:00:00.000
1 ABC11 PM235 Finish Milestone NULL 2016-02-25 00:00:00.000
1 ABC12 WM310 Finish Milestone NULL 2017-09-29 00:00:00.000
My output should look like:
PID AId Mcode AType StartDate EndDate
1 ABC1 PM105 Start Milestone 2013-08-12 00:00:00.000 NULL
1 ABC6 PM200 Start Milestone 2014-08-11 00:00:00.000 NULL
1 ABC11 PM235 Finish Milestone NULL 2016-02-25 00:00:00.000
1 ABC12 WM310 Finish Milestone NULL 2017-09-29 00:00:00.000
You can use the below sql scripts:
Create table MilestoneData
(
ProjectID int,
ActivityId varchar(10),
MileStoneCode varchar(5),
ActivityType varchar(50),
StartDate datetime,
EndDate Datetime)
insert into MilestoneData values(1,'ABC1','PM105','Start Milestone','2013-08-12 00:00:00.000',NULL)
insert into MilestoneData values(1,'ABC2','PM200','Start Milestone','2015-06-22 00:00:00.000',NULL)
insert into MilestoneData values(1,'ABC3','PM200','Start Milestone','2014-08-25 00:00:00.000',NULL)
insert into MilestoneData values(1,'ABC4','PM200','Start Milestone','2014-09-29 00:00:00.000',NULL)
insert into MilestoneData values(1,'ABC5','PM200','Start Milestone','2014-08-11 00:00:00.000',NULL)
insert into MilestoneData values(1,'ABC6','PM200','Start Milestone','2014-08-11 00:00:00.000',NULL)
insert into MilestoneData values(1,'ABC7','PM235','Finish Milestone',NULL,'2015-11-10 00:00:00.000')
insert into MilestoneData values(1,'ABC8','PM235','Finish Milestone',NULL,'2015-11-18 00:00:00.000')
insert into MilestoneData values(1,'ABC9','PM235','Finish Milestone',NULL,'2015-11-10 00:00:00.000')
insert into MilestoneData values(1,'ABC10','PM235','Finish Milestone',NULL,'2015-09-03 00:00:00.000')
insert into MilestoneData values(1,'ABC11','PM235','Finish Milestone',NULL,'2016-02-25 00:00:00.000')
insert into MilestoneData values(1,'ABC12','WM310','Finish Milestone',NULL,'2017-09-29 00:00:00.000')
Upvotes: 1
Views: 111
Reputation: 5040
One way to approach this is by ranking your records, using the windowed function ROW_NUMBER.
Here I've added a start and end rank, calculated inside an CTE. I've used a CTE for two reasons. Firstly it's easier for you to run the query independently and see how it works. Secondly you cannot use windowed functions directly in the WHERE clause.
The WHERE clause uses a CASE expression to choose which ranking field to filter on. In both cases we want the lowest rank (1).
Example
WITH Ranked AS
(
/* CTE ranks each record based on Start and End dates.
*/
SELECT
ROW_NUMBER() OVER (PARTITION BY MileStoneCode ORDER BY StartDate ASC) AS StartRank,
ROW_NUMBER() OVER (PARTITION BY MileStoneCode ORDER BY EndDate DESC) AS EndRank,
ms.*
FROM
MilestoneData AS ms
)
SELECT
*
FROM
Ranked
WHERE
/* Case expression returns only the first ranked record,
* where ranking field is based on MileStonCode.
*/
CASE MileStoneCode
WHEN 'Start Milestone' THEN StartRank
ELSE EndRank
END = 1
;
Worth highlighting that when using CTEs any preceding statements must be terminated, with a semicolon.
Upvotes: 2
Reputation: 2651
This should produce what you're looking for (ran it against the create script you provided):
SELECT projectID, ActivityId, MilestoneCode, ActivityType, startdate,
enddate
FROM MilestoneData
WHERE MilestoneCode + Right(activityId, Len(activityid)-3) + Cast(startdate AS VARCHAR) IN
(SELECT MilestoneCode + Cast(Max(Cast(Right(activityid, Len(activityid)-3) AS INT)) AS VARCHAR) + Cast(Min(startdate) AS VARCHAR)
FROM MilestoneData
WHERE activityType = 'Start Milestone'
GROUP BY milestonecode)
UNION SELECT projectID, ActivityId, MilestoneCode, ActivityType, startdate, enddate
FROM MilestoneData
WHERE MilestoneCode + Right(activityId, Len(activityid)-3) + Cast(enddate AS VARCHAR) IN
(SELECT MilestoneCode + Cast(Max(Cast(Right(activityid, Len(activityid)-3) AS INT)) AS VARCHAR) + Cast(Max(enddate) AS VARCHAR)
FROM MilestoneData
WHERE activityType = 'Finish Milestone'
GROUP BY milestonecode)
EDIT: corrected statement
Upvotes: 1
Reputation: 67341
Try it like this:
WITH DistinctMileStoneCodes AS
(
SELECT DISTINCT ProjectID,MileStoneCode
FROM MilestoneData
)
SELECT dms.ProjectID
,dms.MileStoneCode
,StartAndFinish.*
FROM DistinctMileStoneCodes AS dms
CROSS APPLY
(
SELECT TOP 1 x.ActivityId,x.ActivityType,x.StartDate,x.EndDate FROM MilestoneData AS x WHERE x.ProjectID=dms.ProjectID AND x.MileStoneCode=dms.MileStoneCode AND ActivityType='Start Milestone' ORDER BY StartDate ASC
UNION SELECT TOP 1 x.ActivityId,x.ActivityType,x.StartDate,x.EndDate FROM MilestoneData AS x WHERE x.ProjectID=dms.ProjectID AND x.MileStoneCode=dms.MileStoneCode AND ActivityType='Finish Milestone' ORDER BY EndDate DESC
) AS StartAndFinish
The result
ProjectID MileStoneCode ActivityID ActivityType StartDate EndDate
1 PM105 ABC1 Start Milestone 2013-08-12 NULL
1 PM200 ABC5 Start Milestone 2014-08-11 NULL
1 PM235 ABC11 Finish Milestone NULL 2016-02-25
1 WM310 ABC12 Finish Milestone NULL 2017-09-29
Upvotes: 3
Reputation: 306
You want something like this:
SELECT MileStoneCode,
MIN(StartDate)
FROM MilestoneData
WHERE ActivityType = 'Start Milestone'
GROUP BY MileStoneCode;
SELECT MileStoneCode,
MAX(EndDate)
FROM MilestoneData
WHERE ActivityType = 'Finish Milestone'
GROUP BY MileStoneCode;
Check the fiddle here: http://sqlfiddle.com/#!9/edb508/7
Upvotes: 1