sql - How to find the recent date -

data :

id  |status| date   | what_i_need -------------------------------  1   |c     |jan-15  |jan-15 1   |c     |feb-15  |feb-15 1   |d     |mar-15  |feb-15 1   |d     |apr-15  |feb-15 1   |c     |may-15  |may-15 1   |c     |jun-15  |jun-15 1   |d     |jul-15  |jun-15 1   |d     |aug-15  |jun-15 

need capture max date when status changes d.

what tried :

select t.id, t.status, t.date, case when status = 'd' d.last_current else t.date end what_i_need table t left outer join ( select id, max(date) last_current table t status = 'c' group id) d on d.id= t.id 

the error top part is returning below result :

id  |status| date   | what_i_need ----------------------------------  1   |c     |jan-15  |  jan-15 1   |c     |feb-15  |feb-15 1   |d     |mar-15  |jun-15  <----this wrong should feb 15 1   |d     |apr-15  |jun-15  <----this wrong should feb 15 1   |c     |may-15  |may-15 1   |c     |jun-15  |jun-15 1   |d     |jul-15  |jun-15 1   |d     |aug-15  |jun-15  

i describe need recent date (including current one) when status not 'd'. @ least explains last column.

i use correlated subquery logic:

select t.*,        (select max(t2.date)         table t2         t2.date <= t.date ,               t2.status <> 'd'        ) what_i_need table t;