...
Then we save these numbers to “variables” (results of common table expressions) just for convenience:
| Code Block |
|---|
| breakoutMode | widefull-width |
|---|
| language | sql |
|---|
|
status_field as (
select num from field_number_mapping where field = 'status' limit 1
),
sprint_field as (
select num from field_number_mapping where field = 'sprint' limit 1
),
story_points_field as (
select num from field_number_mapping where field = 'story_points' limit 1
), |
Define what are the closed statuses
| Code Block |
|---|
| breakoutMode | widefull-width |
|---|
| language | sql |
|---|
|
closed_statuses as (
select a
from (
values ('Closed'), ('Verified')
) s(a)
), |
...
Get the Sprint you want to count the Burndown Chart for
| Code Block |
|---|
| breakoutMode | widefull-width |
|---|
| language | sql |
|---|
|
last_sprint as ( -- get the last started sprint
select * from Sprint where start_date is not null and state = 'ACTIVE' order by start_date desc limit 1), |
Then just for convenience save the search expression for this sprint name
| Code Block |
|---|
| breakoutMode | widefull-width |
|---|
| language | sql |
|---|
|
last_sprint_like as (
select concat('%', (select id from last_sprint), '%')
), |
...
Get the Tickets that are in any way related to the sprint we chose
| Code Block |
|---|
| breakoutMode | widefull-width |
|---|
| language | sql |
|---|
|
affected_tickets as ( -- get all tickets somehow related to this sprint
select distinct(t.*)
from Ticket t
join TicketHistory th on th.workitem_id = t.id
where th.sprint like concat('%', (select id from last_sprint),'%') -- Ticket was ever added to sprint
or (select id from last_sprint) = any(t.sprints) /* Ticket is in sprint right now (including the case when it was added
to sprint on ticket creation, which is not usually mentioned in Jira history)*/
), |