Paul Hartland
paul.hartland at googlemail.com
Tue Mar 24 07:54:25 CDT 2009
Kaup, Very quick off top of my head, try the following: SELECT PID, CASE WHEN ProdMonth>ProdMonth1 AND ProdMonth>ProdMonth2 THEN ProdMonth ElSE CASE WHEN ProdMonth1>ProdMonth AND ProdMonth1>ProdMOnth2 THEN ProdMonth1 ELSE ProdMonth2 END END AS LatestDate, CASE WHEN ProdMonth>ProdMonth1 AND ProdMonth>ProdMonth2 THEN Type ElSE CASE WHEN ProdMonth1>ProdMonth AND ProdMonth1>ProdMOnth2 THEN Type1 ELSE Type2 END END AS LatestType FROM [yourtablename] GROUP BY PID, ProdMonth, ProdMonth1, Prodmonth2, [Type], Type1, Type2 Paul Hartland 2009/3/24 Kaup, Chester <Chester_Kaup at kindermorgan.com> > I have a table with a unique identifier column three date columns and a > fluid type associated with each date. I need to extract from the table the > most recent of the three dates for each record and its associated fluid > type. I considered some kind of complex if statement but surely there is a > better way. Below is a sample record. > > PID Status ProdMonth Type ProdMonth1 Type1 ProdMonth2 > Type2 > 42415016970000 PA 4/1/1963 Oil 6/1/2003 CO2 7/1/2007 Water > > Chester Kaup > Engineering Technician > Kinder Morgan CO2 Company, LLP > Office (432) 688-3797 > FAX (432) 688-3799 > > > No trees were killed in the sending of this message. However a large number > of electrons were terribly inconvenienced. > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Paul Hartland paul.hartland at googlemail.com