This seems to be very easy (and probably is!!) but I'm just stuck here.
I have a table that contains an ID, a date and a Status.
I want to return the top row (most recent datetime) if any of the status for the same Id is not C (as in Canceled).
Here's an example:
create table #Temp1
(RecID int, DateEntered Datetime, Status Varchar(1))
INSERT INTO #Temp1 VALUES (1,'01-01-2013 10:20:10', 'N')
INSERT INTO #Temp1 VALUES (1,'01-02-2013 08:14:00', 'R')
INSERT INTO #Temp1 VALUES (1,'01-03-2013 03:30:00', 'E')
INSERT INTO #Temp1 VALUES (2,'01-01-2013 14:58:00', 'N')
INSERT INTO #Temp1 VALUES (2,'01-02-2013 08:23:00', 'R')
INSERT INTO #Temp1 VALUES (2,'01-04-2013 22:14:00', 'C')
select * from #Temp1
drop table #temp1
In this examples my result set should be:
1 ; 01-03-2013 03:30:00 ; E
No record should be returned from RecId 2 because the last line is a 'C'.
The 'C' status will always be in the last row.
Thank you all for your help!!