Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
6616 dpurdie 1 
-- Determine the projects that have release mertics
2 
-- Select the most recently built release for each project
3 
-- Used in the Release Statistics summary page
4 
WITH ORDERED as (
5 
SELECT p.proj_id,
6 
       p.proj_name,
7 
       rt.rtag_name,
8 
       rm.rtag_id,
9 
       rm.total_packages,
10 
       rm.autobuilt,
11 
       rm.lines_of_code,
12 
       rm.unit_tested,
13 
       rm.autotested,
14 
       TO_CHAR( rm.last_build_time,'DD-Mon-YYYY HH24:MI:SS' ) AS last_build_time,
15 
       ROW_NUMBER() OVER (PARTITION BY p.proj_id ORDER BY CASE
16 
         WHEN rm.last_build_time IS NULL THEN 2 ELSE 1
17 
       END,
18 
       rm.last_build_time DESC
19 
       ) AS rn
20 
  FROM projects p,
21 
       release_metrics rm,
22 
       release_tags rt
23 
 WHERE p.proj_id = rt.proj_id
24 
   AND rm.rtag_id = rt.rtag_id
25 
 ORDER BY
26 
       Upper(p.PROJ_NAME),
27 
       CASE
28 
         WHEN rm.last_build_time IS NULL THEN 2 ELSE 1
29 
       END,
30 
       rm.last_build_time DESC,
31 
       UPPER(rt.rtag_name),
32 
       rm.rtag_id DESC
33 
)
34 
SELECT * from ORDERED where rn=1
35