Finding claims after the status 'case closed'

[zuza]

Dear all,

I will start wil an example, it is a table with history like:

Claimno ---- Status ---- Datest
ds001123 notified 12/01/04
ds001123 Case closed 12/03/04
ds001123 decision 12/09/04

By the rule 'Case closed' should be the last status, so how to find records like one above, that after the case closed is entered status ('decision', in this case) with higher date and it shoul be before the status 'case closed'

I you understood me, I hope that you will help me.

Rgds,
ZuZa

 

[hneal98]

You can do something like this:

select tt1.* 
from
	#tmpTest tt1
	inner join #tmpTest tt2 on tt1.MyID = tt2.MyID
where
	tt1.Status = 'Case Closed'
	and tt1.StatusDate < tt2.StatusDate

 

[dreameR25]

select	a.*
from	yourTable as a
where	a.datest = (select max(datest)
			from yourTable as b
			where	a.claimno = b.claimno)
and	Status <> 'Case closed'

Reality of a dreameR.