Query to found the sprint status of tickets on sepcific day?

[Stefan Jacomeit]

Hello,

it is possible to show the ticket status (open/in-process) of a sprint of a specific date / day with a query? And if so, how should the query look like?

Thanks,

Stefan

[Piotr Bojko]

Hello,

You should able to to this with Smart QL  https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview.

SELECT MAX(aggr.checkedday) lastchanged, aggr.issuekey, ANY_VALUE(status) -- your desired result
FROM
 (SELECT AUX.ASISSUEKEY(ch.issue) issuekey, FLOOR(ch.CREATED TO DAY) checkedday, FIRST_VALUE(ch.newstring) OVER W status -- each status at the end of a checkday (see window down here)
 FROM TABLE(AUX.JQL('id','project = robustus')) jql -- join with your sprint taken from JQL query result as a table, this is a qool feature of Smart QL:)
 JOIN changes ch ON ch.issue = jql.id -- here there are all historical changes to the issue
 WHERE (ch.fieldtype = 'jira' AND ch.field = 'status' -- these changes are for statuses
 AND ch.created < TIMESTAMP '2017-02-05 00:00:00') -- look for all changes before your desired date
 WINDOW W AS (PARTITION BY ch.ISSUE ORDER BY ch.ID DESC)) aggr -- this window allows you to check status for each issue 
GROUP BY aggr.issuekey

This will show you all issues  with a state on the end of the 2017-02-04 day. But only if they are  not leaved open. For all issues, including not touched since their creation (such issues are not logged in changes view which smart ql provides) you should outer join the result of JQL and Changes view. For the sake of clarity I didn't do that.

See docs for Smart QL - https://codedoers.atlassian.net/wiki/spaces/SUPPORT/pages/186712065/Getting+started+with+Smart+QL or ask the vendor support for help with this.

[Piotr Bojko]

Please note that this query gives only data you can access in JIRA in normal way - it won't show you issues you want see in JIRA normally. Smart QL supports JIRA permissions system.

[Stefan Jacomeit]

Ok thanks.. i will try it.

[Piotr Bojko]

Having some thoughts that I gave you incomplete answer. 

I believe the whole query would like as follows

select steps.id issue, steps.oldstring step, steps.newstring nextstep, steps.lagged fromdate, steps.created todate from
(select I.ID, CH.OLDSTRING, CH.NEWSTRING, lag(CH.CREATED, 1, I.CREATED) over W lagged, CH.CREATED
from ISSUES I
left outer join CHANGES CH on I.ID = CH.ISSUE
where CH.FIELDTYPE = 'jira' and ch.field = 'status'
window W as (partition by I.ID order by ch.id)) steps
join table(aux.JQL('id','project = robustus')) jql on steps.id = jql.id
where TIMESTAMP '2017-08-30 16:14:20' >= steps.lagged and (TIMESTAMP '2017-08-30 16:14:20' < steps.created or steps.created is null)

This what Smart QL is capable of :)

Don't hesitate to contact vendor of Smart QL for further questions.