SQL & PL/SQL

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
--------------------------------------------------------------------------------

We have a table that has an identifier, a document type column and multiple status dates (around 42). We want to find the maximum date between the 10 dates in the same record and also find the column name for that date. Then we bring back to the user data that says that for that ssn, for that document type, the current status of that document type is the column name with the highest date.

For example for an application with identifier = 1, if the begin_date is 1/1/12 and the submit_date is 2/1/12 and the reject_date is 3/1/12 then we bring back identifier =1, status=rejected, date = 3/1/12. We know that the initial design should have had a status column in the table, but you are preaching to the choir.

The solution we have right now is

table definition

data_table
(pk number,
identifier number,
document_type number,
begin_date date,
submit_date date,
reject_date date,
........
date42 date);



with temp
as
(select identifier ,
document_type,
'begin date' as date_type,
begin_date as doc_date
from data_table
union all
select identifier ,
document_type,
'submit date' as date_type,
submit_date as doc_date
from data_table
union all
select identifier ,
document_type,
'reject date' as date_type,
reject_date as doc_date
from data_table)
select
date_type,
doc_date,
identifier,
document_type
from temp T
where t.doc_date = (select max(b.doc_date)
from temp b
where b.identifier = t.identifier);

and it works. But this is painful since it is a union for every date column in the table.

We are looking for a more elegant solution that will be easier to maintain using pivots or something else.

Thanks in advance.