question

poornima.narayanan avatar image
poornima.narayanan asked

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

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')
sql server 2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
Can you please clarify whether the ActivityID (or AId) you have selected in your desired results for each row is the result of a MAX (although slightly disguised by the Alpha/Numeric problem) function OR is it the ActivityID that is contained on the same row as the MIN Start Date / MAX End Date selected? Your sample data "happens" to come out the same either way, but that needs clarifying.
0 Likes 0 ·
Gazz avatar image
Gazz answered
Sorry, I noticed that my last answers was wrong You wanted Min Start and Max End date, but I gave Max to both. This will do it - the tricky bit was that your ActivityId had a string and a integer, so when calculating max of a column it would know ABC2 > ABC1, but thing ABC9 > ABC11. I got round this by just taking the last 1 or two digits (depending on how many chars there where): SELECT m.ProjectID , m.ActivityId , m.MileStoneCode , m.ActivityType , m.StartDate , b.EndMileDate [EndDate] FROM milestonedata m JOIN ( SELECT A1.* , A2.StartMileDate FROM ( SELECT milestonecode , MAX(RIGHT(ActivityId, LEN(ActivityId) - 3) * 1) [ActivityId] , MAX(ENDDATE) [EndMileDate] FROM milestonedata GROUP BY MileStoneCode ) A1 JOIN ( SELECT milestonecode , MIN(StartDate) [StartMileDate] FROM milestonedata GROUP BY MileStoneCode ) A2 ON A1.MileStoneCode = A2.MileStoneCode ) b ON m.milestonecode = b.milestonecode WHERE RIGHT(m.ActivityId, LEN(m.ActivityId) - 3) = b.ActivityId
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Morrison avatar image
Dave Morrison answered
**EDITED to suit needs, thanks Gaz** This should do what you need if I've understood correctly select ProjectID ,ActivityID ,MilestoneCode ,ActivityType ,StartDate ,EndDate from ( select ProjectID ,ActivityID ,MilestoneCode ,ActivityType ,StartDate ,EndDate ,ROW_NUMBER() over (partition by MilestoneCode, ActivityType order by StartDate asc, ActIDNum desc) as SD_RN ,ROW_NUMBER() over (partition by MilestoneCode, ActivityType order by EndDate Desc, ActIDNum desc) as ED_RN from MilestoneData cross apply(values(right(ActivityId, patindex('%[^0-9]%', ActivityId)))) AI (ActIDNum) ) as D where (StartDate is not null and SD_RN = 1) or (EndDate is not null and ED_RN = 1)
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gazz avatar image Gazz commented ·
This returns every row back - my understanding is that you should only return a row if the Start Date is the smallest or the End Date is the largest
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
There is a bit of ambiguity in the question for sure but OP said "I want to show only one row for each mcode". Its odd as there's no duplication of mcode in the test data set but given his expected result set (which I'm assuming is a sample subset) I'm not sure how you've inferred they only wanted the rows with the min start and the max end?
0 Likes 0 ·
Gazz avatar image Gazz commented ·
Milestone code is the PM thing, ABC2 and ABC3 both have the same MilestoneCode of PM200 I think you are getting it confused with ActivityID which seems to be the PK for the table
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
Ah yeah spotted it now! Will adjust
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
Think this will do the do
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.