Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2002-09-27 : 11:44:28
|
| I am back from vacation andneed a jump start, think I drank too much Hoff Brau.I am attempting to update a status field and a date basedon two tables. CE and CX.CECEIDCaseidCXCEID Exitdatea CE record must exist to have a CX record, CX may or may not exist.There can be many CE records for a given caseid.if there is a record in CE but not in CX, I want the record to show exitdate is NULL, however, if a given caseid's CE records all have an exitdate, I want the most recent date.I started by sticking all the joined fields into a temp table and ordering by exitdate, that puts the NULL's at the top, the problem comes up that if there is a record in CE and in CX, I get the first exitdate for a given caseid, not the most recent.So basically, I want to end up with one record per caseid that shows either a null or the most recent exit date from CX. Anyone following me at all?I can post tables, etc if it will help.Danke Schoen_________________________Beer is healthy, I read it on the internet. It must be true! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-27 : 13:44:04
|
| How about:SELECT CE.CaseID, Max(CX.ExitDate) AS MaxDateFROM CE LEFT JOIN CX ON CE.CEID=CX.CEIDGROUP BY CE.CaseID???Edited by - robvolk on 09/27/2002 13:44:50 |
 |
|
|
|
|
|
|
|
sql null or newest date