| Wiki Markup |
|---|
{replace-and-render}
//projeto
{replace-item:%projeto%}JBTRT4ADME{replace-item}
//sprint
{replace-item:%sprint%}Sprint 28{replace-item}
//data inicial
{replace-item}{replace-item}
//data final
{replace-item}{replace-item}
//usuario que trabalhou
{replace-item:%usuario%}marcelo.mariano{replace-item}
{replace-item:%open%body}
select cast((count(*)/(select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%JBTRT4ADME%')--nome do projeto
and pv.vname like('%Sprint 29%')--nome da versao
and ji.issuestatus in('1')))as float) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project
{chart:type=line|title=Burnup de teste|dataOrientation=vertical|subTitle=Acompanhamento da sprint com sql|width=600|height=300|dataDisplay=false|yLabel=Projeto Concluído em %|xLabel=Em dias|3D=false|borderColor=black|colors=yellow,green|thumbnail=true}
|| Dias || Esperado || Pronto ||
| 01 | 6.66 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%JBTRT4ADME%%projeto%')--nome do projeto
and pv.vname like('%Sprint 29%%sprint%')--nome da versao
and ji.issuestatus in('1','3','5')
{replace-item}
{replace-item:%inProgress%}
{sql-query:datasource=jiraDS|table=false|}
select cast(())*6.66/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%JBTRT4ADME%%projeto%')--nome do projeto
and pv.vname like('%Sprint 29%%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus,'5')
{sql-query}
|
| 02 | 13.32 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%JBTRT4ADME%%projeto%')--nome do projeto
and pv.vname like('%Sprint 29%%sprint%')--nome da versao
and ji.issuestatus in('1','3','5')
{sql-query}
{replace-item}
{replace-item:%resolved%}
{sql-query:datasource=jiraDS|table=false|}
select cast((count(*)/(select count(*)3'))*13.32/count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%JBTRT4ADME%%projeto%')--nome do projeto
and pv.vname like('%Sprint 29%%sprint%')--nome da versao
and ji.issuestatus in('1','3','5')))as float) as openstatus from jiraissue ji, project
{sql-query} |
| 03 | 19.98 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%JBTRT4ADME%%projeto%')--nome do projeto
and pv.vname like('%Sprint 29%%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query}
{replace-item}
{replace-body}
{chart:type=bar|title=Burnup de teste com sql|dataOrientation=vertical|width=600|height=300|dataDisplay=false|3D=false|colors=green,blue}
|| Dias || Esperado || Pronto ||
| 01 | 6.66 | {sql-query:datasource=jiraDS|table=false|}select cast((count(*)/(select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1')))as float) as openstatus3'))*19.98/count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')
and ji.issuestatus in('1','3','5')
{sql-query} |
| 04 | 26.64 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')
--nome da versao
and ji.issuestatus in('1','3','5'){sql-query}
|
| 02 | 13.32 | {sql-query:datasource=jiraDS|table=false|}select cast(())*26.64/count(*)/(select count(*) fromas openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5')))as float) as openstatus from jiraissue ji, project p,
{sql-query} |
| 05 | 33.33 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 03 | 19.98 | {sql-query:datasource=jiraDS|table=false|}select cast((count(*)/(select count(*) from jiraissue ji, project p, 3'))*33.33/count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus,'5')
{sql-query} |
| 06 | 39.96 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 04 | 26.64 | {sql-query:datasource=jiraDS|table=false|}select cast((count(*)/(select count(*)))*39.96/count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 07 | 46.62 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 05 | 33.33 | {sql-query:datasource=jiraDS|table=false|}select cast(())*46.62/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 08 | 53.28 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 06 | 39.96 | {sql-query:datasource=jiraDS|table=false|}select cast(())*53.28/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 09 | 59.94 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 07 | 46.62 | {sql-query:datasource=jiraDS|table=false|}select cast(())*59.94/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 10 | 66.60 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 08 | 53.28 | {sql-query:datasource=jiraDS|table=false|}select cast(())*66.6/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 11 | 73.26 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 09 | 59.94 | {sql-query:datasource=jiraDS|table=false|}select cast(())*73.26/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 12 | 79.92 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 10 | 66.60 | {sql-query:datasource=jiraDS|table=false|}select cast(())*79.92/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 13 | 86.58 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 11 | 73.26 | {sql-query:datasource=jiraDS|table=false|}select cast(())*86.58/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3')))as float) as openstatus','5')
{sql-query} |
| 14 | 93.24 | {sql-query:datasource=jiraDS|table=false|}
select ((select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5'){sql-query} |
| 12 | 79.92 | {sql-query:datasource=jiraDS|table=false|}select cast(())*93.24/count(*)/(select count(*)) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('1','3','5')))as float) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')
and ji.issuestatus in('1','3','5'){sql-query} |
| 13 | 86.58 | {sql-query:datasource=jiraDS|table=false|}select cast((count(*)/(select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('5')))as float) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')
and ji.issuestatus in('1','3','5'){sql-query} |
| 14 | 93.24 | {sql-query:datasource=jiraDS|table=false|}select cast((count(*)/(select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('5')))as float) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')
and ji.issuestatus in('1','3','5'){sql-query} |
| 15 | 100 | {sql-query:datasource=jiraDS|table=false|}select cast((count(*)/(select count(*) from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')--nome da versao
and ji.issuestatus in('5')))as float) as openstatus from jiraissue ji, project p, projectversion pv
where ji.project = pv.project
and pv.project = p.id
and p.pkey like('%projeto%')--nome do projeto
and pv.vname like('%sprint%')
and ji.issuestatus in('1','3','5'){sql-query} |
{chart}
{sql-query:datasource=jiraDS|table=false|}
select count(*) from jiraissue
{sql-query}
{sql-query:datasource=jiraDS|table=false|}
select v.description from projectversion v inner join project p on v.project = p.id and p.pkey like '%projeto%' and v.vname like '%sprint%'
{sql-query}
{html}
<ul>
{html}
{sql-query:datasource=jiraDS|table=false|output=html|endRow=</li>|}
select '<li>'|| issue.summary from projectversion v, nodeassociation n, jiraissue issue, customfield cf, customfieldvalue cv where
n.source_node_entity='Issue' and n.sink_node_entity='Version' and
n.association_type='IssueFixVersion' and n.sink_node_id =
v.id and n.source_node_id = issue.id and issue.pkey like '%JBTRT4ADME-%' and v.vname = '%versao%'
and cf.cfname = 'Parte da Meta' and cf.id = cv.customfield and cv.issue = issue.id and cv.stringvalue = 'Sim'
{sql-query}
{html}
<ul>
{html}
{
{sql-query} |
| 15 | 100 | 0 |
{chart}
Eis aqui um exemplo de o que é para acontecer no gráfico com os percentuais de execução em cada dia de uma sprint de 15 dias.
* primeiro dia deve, mostra o percentual de issues com status 'Open'.
* segundo dia em diante deve mostrar o percentual de issues com status 'InProgress' até 70% do projeto.
* entre 70% e 95% mostra o percentual de issues com status 'Resolved' até 95%.
{replace-body}
{replace-and-render} |
Teste passando apenas um sql.