Subversion Repositories DevTools

--  Select test count for packaages in a specified release
--  CLEAN_BI - a cleaned up version of BUILD_INSTANCES 
--  Select the 'latest'  build instance of the package
--
with CLEAN_BI as (
select pv_id, max(build_id) as build_id  from BUILD_INSTANCES bi
where bi.state in ('C')
and bi.reason not in ('T')
group by pv_id
)
select rc.pv_id,cbi.build_id, pv.pkg_id, p.PKG_NAME, pv.V_EXT, p.PKG_NAME || pv.V_EXT as pkg_alias, pv.PKG_VERSION,
  (select count(*) from test_run tr where tr.build_id = cbi.build_id) as test_count, 
  TO_CHAR(bi.timestamp,'DD-Mon-YYYY HH24:MI:SS') AS lastBuild
from RELEASE_CONTENT rc, PACKAGE_VERSIONS pv, packages p, CLEAN_BI cbi, BUILD_INSTANCES bi
where rc.RTAG_ID = :RTAG_ID
and rc.pv_id = pv.pv_id
and p.pkg_id = pv.pkg_id
and cbi.pv_id = pv.pv_id
and cbi.build_id = bi.build_id
order by upper(p.pkg_name)