By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with TestCaseStatus as (
select 'ABC' TestName, 'Passed' Status, to_date('11:10', 'hh24:mi') Timestamp from dual
union all select 'ABC', 'Passed', to_date('11:00', 'hh24:mi') from dual
union all select 'ABC', 'Failed', to_date('10:50', 'hh24:mi') from dual
union all select 'EFG', 'Passed', to_date('11:00', 'hh24:mi') from dual
union all select '123', 'Failed', to_date('11:10', 'hh24:mi') from dual
union all select '123', 'Passed', to_date('11:00', 'hh24:mi') from dual
)
select testname,
(case when max(status) keep (dense_rank first order by timestamp desc) = 'Passed' and
sum(case when status = 'Failed' then 1 else 0 end) > 0
then 'Passed_On_Rerun'
else max(status) keep (dense_rank first order by timestamp desc)
end)
from TestCaseStatus tcs
group by testname
| TESTNAME | (CASEWHENMAX(STATUS)KEEP(DENSE_RANKFIRSTORDERBYTIMESTAMPDESC)='PASSED'ANDSUM(CASEWHENSTATUS='FAILED'THEN1ELSE0END)>0THEN'PASSED_ON_RERUN'ELSEMAX(STATUS)KEEP(DENSE_RANKFIRSTORDERBYTIMESTAMPDESC)END) |
|---|---|
| 123 | Failed |
| ABC | Passed_On_Rerun |
| EFG | Passed |