i have in tb1 data like and i need the latest status changed date : when date on which the status of the task changes.
for the data below for taskno :1 i need to get the o/p : 1/2/2013
and for taskno :2 i need the o/p 1/4/2013
taskno | upd_date | old_status | new_status |
1 | 1/1/2013 | open | WIP |
1 | 1/2/2013 | WIP | Closed |
1 | 1/3/2013 | closed | closed |
2 | 1/1/2013 | open | WIP |
2 | 1/2/2013 | WIP | more_info_needed |
2 | 1/3/2013 | more_info_needed | WIP |
2 | 1/4/2013 | WIP | closed |
im writing the query as below
select taskno, max(upd_date) MAX_UPD_DATE from (
select taskno, upd_date, old_status, new_status, rank from (
select taskno, upd_date, old_status, new_status, rank() over (partition by taskno, old_status, new_status order by upd_date asc) as rank
from tb1
)
where rank = 1
and old_status <> new_status)
group by taskno;
IS there any more efficient way to write this query as the clause "old_status <> new_status" is taking a toll on the performance