Subversion Repositories DevTools

-- UtfCounts
-- Calculate the total unit tests for each Release within a Project
-- Based on the latest Non-Test Non-Error build
WITH UtfCounts as (
    SELECT rc.rtag_id, SUM ((SELECT COUNT(*) FROM test_run tr WHERE tr.build_id = cbi.build_id)) AS total_test_count
    FROM package_versions pv,
      release_content rc,
      (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
          ) cbi,
      BUILD_INSTANCES bi,
      release_tags rt
    WHERE pv.pv_id   = rc.pv_id
    AND rt.rtag_id = rc.rtag_id
    AND rt.proj_id = :PROJ_ID
    AND cbi.pv_id(+) = pv.pv_id
    AND cbi.build_id = bi.build_id(+)
    GROUP by rc.rtag_id
) 
SELECT p.proj_id,
       p.proj_name,
       rt.rtag_name,
       rt.OFFICIAL,
       rm.rtag_id,
       rm.total_packages,
       rm.autobuilt,
       rm.lines_of_code,
       rm.unit_tested,
       rm.autotested,
       CASE
         WHEN rm.branches = 0 THEN NULL ELSE rm.branches
       END AS branches,
       TO_CHAR( rm.last_build_time,'DD-Mon-YYYY HH24:MI:SS' ) AS last_build_time,
       u.total_test_count
  FROM projects p,
       release_metrics rm,
       release_tags rt,
       UtfCounts u
 WHERE p.proj_id = rt.proj_id
   AND rm.rtag_id = rt.rtag_id
   AND p.proj_id = :PROJ_ID
   AND u.rtag_id = rt.rtag_id
 ORDER BY
       CASE
         WHEN rm.last_build_time IS NULL THEN 2 ELSE 1
       END,
       rm.last_build_time DESC,
       UPPER(rt.rtag_name),
       rm.rtag_id DESC