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_status", "curr_status"
from
(
select contract_number, Product_code, trandate, lag(status_code) over (order by trandate asc) "prev_status",
status_code "curr_status" from d_contract_hist
where contract_number = '0001'
order by trandate asc
)
where "prev_status" <> "curr_status"
and "prev_status" is not null