query

[santoshdhote]

hi to all,
i have a table with following structure
contract_number trandate status_date
1 01-jan-07 a
2 01-jan-07 a
1 05-feb-07 b
3 04-jan-07 a

now i have to retrive this data in following format

contract_number trandate previous_status _date current_status
1 01-jan-07 a b

can anybudy plz tell me how to achiv this.

[debasisdas]

KIndly post how you have tried to solve this with what is the database that you are using for the pupose.

[santoshdhote]

KIndly post how you have tried to solve this with what is the database that you are using for the pupose.

database :ORACLE 9i
i'm writing following query,for single record it gives proper result,but whole records it gives mismatch data.


select contract_number , Product_code, trandate, "prev_statu s", "curr_statu s"
from
(
select contract_number , Product_code, trandate, lag(status_code ) over (order by trandate asc) "prev_statu s",
status_code "curr_statu s" from d_contract_hist
where contract_number = '0001'
order by trandate asc
)
where "prev_statu s" <> "curr_statu s"
and "prev_statu s" is not null