How to achieve this SQL Query ? Min(Date1) and Max(Date2)

Question

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')

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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