1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| SELECT TRUNC(
sum(
decode(
changeitem.newstring,'Closed',decode(
changeitem.groupid,changegroup.id,decode(
changegroup.issueid,jiraissue.id,1,0
),0
),0
)
)/(SELECT count(jiraissue.id)
FROM jiraissue JOIN project ON( jiraissue.project=project.id)JOIN priority ON(jiraissue.priority=priority.id ) AND project.pname IN ('project1')),2
) AS avgBackAndForth,
priority.pname AS champ1,
project.pname AS champ2
FROM changeitem
JOIN changegroup ON (changeitem.groupid=changegroup.id)
JOIN jiraissue ON (jiraissue.id=changegroup.issueid)
JOIN project ON( jiraissue.project=project.id)
JOIN priority ON(jiraissue.priority=priority.id )
AND project.pname IN ('project1')
AND jiraissue.CREATED BETWEEN to_date('2011-05-18 00:00:00','yyyy-mm-dd HH24:MI:SS') AND to_date('2011-06-04 00:00:00','yyyy-mm-dd HH24:MI:SS')
GROUP BY priority.pname,project.pname ; |
Partager