Select Null over Valid Date

[mkelly11]

I have a multi table join, where I need to select the record with an "open" end date...for example

ID Start End
123 01/01/02 12/31/02
123 01/01/03 null
456 01/01/04 04/01/04
456 04/02/04 null
789 01/01/03 01/31/04

In the scenario where ID's 123 & 456 have multiple records I only want to capture the records with null End Dates, however in the case of ID 798 I would want to capture that single record.

This is probably the simplest answer in the world and I have just over analyzed it, please help!!

 

[evaleah]

FROM Table1

WHERE
End is NULL
OR
End = CASE
WHEN not exists(SELECT End FROM Table WHERE End is NULL and Table.Start = Table1.Start) THEN (SELECT Max(EndDate) FROM Table WHERE Table.Start = Table1.Start) END

I have to admit to not having tested this, but it should work.