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
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 resultFROM (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.
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.
Ok thanks.. i will try it.
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.CREATEDfrom ISSUES Ileft outer join CHANGES CH on I.ID = CH.ISSUEwhere CH.FIELDTYPE = 'jira' and ch.field = 'status'window W as (partition by I.ID order by ch.id)) stepsjoin table(aux.JQL('id','project = robustus')) jql on steps.id = jql.idwhere 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.
It looks like you're new here. Sign in or register to get started.
