gissah (12/23/2014)
Thanks that is what I ended up doing with a subquery.select pt.projinvoiceprojid,MIN(CAST(pt.PROJECTEDSTARTDATE AS DATE)) AS 'SOPOrderDate_ProjectStartDate'
from projtable pt
where pt.PROJECTEDSTARTDATE !='1900-01-01'
group by pt.projinvoiceprojid
order by pt.projinvoiceprojid
Be careful! Do you really want to ignore / not list at all any projects where all pt.PROJECTEDSTARTDATEs are '19000101' or NULL (the code above will also exclude all NULL dates, if any).
To get the MIN date beyond 19000101, you could do this instead:
select pt.projinvoiceprojid,
MIN(CASE WHEN CAST(pt.PROJECTEDSTARTDATE AS DATE) >= '19000101' THEN CAST(pt.PROJECTEDSTARTDATE AS DATE) END) AS 'SOPOrderDate_ProjectStartDate'
--,...
from projtable pt
where pt.PROJECTEDSTARTDATE !='1900-01-01'
group by pt.projinvoiceprojid
order by pt.projinvoiceprojid
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.