Need to retrieve minimum date based on 2 criteria

[elsenorjose]

Hello everyone,

I'm using SQL Server 2005 and have the following query.

 SELECT "usrProject_Info"."ProjectName"
, "usrProject_Info"."PartNumber"
, "usrProject_Info"."BatchSize"
, "usrProject_Info"."Manufacturer"
, "usrProject_Info"."ProjEndDate"
, "usrProject_Info"."ProjTypeName"
, "usrProject_Info"."PackagingLot"
, "usrProject_Info"."CRO"
, "usrProject_Info"."ProjectPhase"
, "usrProject_Info"."ProductLot"
, "usrProject_Info"."QAReleaseRef"
, "usrProject_Info"."QPRequired"
, "usrProject_Info"."PkgAmt"
, "usrProject_Info"."LblngAmt"
, "usrProject_Info"."PackagingLoc"
, "usrProject_Info"."LabelingLoc"
, "usrProject_Info"."ProjectId"
, "usrProject_Info"."APIManufacturer"
, "usrProject_Info"."ComparatorProdLoc"
, "usrProject_Info"."ReportProjectType"
, "usrTask_ALL"."TaskName"
, "usrTask_ALL"."WorkPercentComplete"
, "usrTask_ALL"."StartDateTimeUTC"
 FROM   "ProjectInsight"."dbo"."usrProject_Info" "usrProject_Info" 
LEFT OUTER JOIN "ProjectInsight"."dbo"."usrTask_ALL" "usrTask_ALL" 
ON "usrProject_Info"."ProjectId"="usrTask_ALL"."ProjectId"

I am trying to retrieve the MIN "usrTask_ALL"."StartDateTimeUTC" when two criteria are met. If the "usrProject_Info"."ReportProjectType" is in ("drug product manufacturing", "GMP API manufacturing", "nGMP API manufacturing") AND "usrTask_ALL"."TaskName" like '%mfg%', then get MIN "usrTask_ALL"."StartDateTimeUTC", otherwise simply retrieve whatever date is stored. The manufacturing projects have multiple mfg tasks and I want only the first task based on the date. The other project types will only have one task so there would only be one date.

I can't remember the correct way of CASEing and then sub-selecting the min date and I'm not doing a good job of understanding the HELP file examples I've been reading.

Thanks in advance.

 

[markros]

What is your SQL Server version?

Please take a look at these two blog posts which may help you to understand how to approach the problem:

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[elsenorjose]

Thanks Markros. My SQL Server version is 2005. I read the posts and am still having a hard time implementing what I need. Any suggestions?

 

[markros]

What did you try so far?

PluralSight Learning Library