Comparing sprints to issues' target end dates?

[Ville Kylmänen]

Hello,

short question on how to compare target end dates to sprint timing.

Context: I'm working for a programme where we have multiple objective issues, which i assigned to (monhtly) sprints, according to our original/fixed schedule. The assignees for each objective are free to change the target end dates as they see fit, to better reflect the realistic scheduling (=typically later than originally scheduled). 

Challenge: I would like to be able to create a JQL query to search for all issues, which have a target end date which is later than the end date of the sprint, to which the issues are assigned. I would need this functionality to make some dashboard reports and to effectively manage the programme. 

So for example: "Issue X" is assigned to a "october 23" sprint ending on October 31st, 2023. Now the target end date is not within the sprint, but on November 30th. I would like to get help on the syntax to find this issue, and all similar issues, where the target end > "[end of their sprint]".  The challenges seems to be, that there is no readily available field for this [end of sprint] -type information.

I discussed this with some of our most senior Jira experts in-house and consulted Atlassian advanced syntax help plus turned to Bing with chatGPT, and it felt like using this needs some Custom Fields (which i cannot seem to find) or some SprintEnd() type field, which apparently is not available in my product. 

So, any tips or ideas how to resolve/circumvent this would be highly appreciated! BR from Helsinki, Finland / Ville

[Tim C · Accepted solution]

Hi @Ville Kylmänen 

FYI. There's an "open" product suggestion for this exact requirement of JQL searching of sprint start/end dates (as raised in 2019) ...

https://jira.atlassian.com/browse/JRACLOUD-72007 

As hinted by your findings - only workaround would probably be storing these Sprint start/end dates in their own custom fields which would then searchable and comparable to the start or due/end dates fields.

You would need to use automation rules to populate these custom fields as Sprint values added onto tickets.

• trigger: Sprint field value changed (or perhaps Sprint started?)
• branch: on JQL to find the assigned issues with sprint IN {{sprint.id}}
• action: edit issue to set Start Date to {{sprint.startDate}} and End/Due Date to {{sprint.endDate}}

I'm not sure there's another native way without the use of automation (unless someone else may know of a plugin)?

I hope this helps.

Tim C

[Bill Sheboy]

Hi @Ville Kylmänen -- Welcome to the Atlassian Community!

Adding to the ideas from Tim...

JQL cannot compare two different issue fields; it is not an actual SQL.  You would need a marketplace addon for such comparisons.

As a work-around...you may want to use automation rules, triggered on changes to either the Target End Date or the Sprint fields, detect the condition, and then add/remove an indicator to the issue (e.g., a label).  That indicator could then be used in a JQL statement to find the issues.

Kind regards,
Bill

[Ville Kylmänen]

Hi, thanks to you both @Tim C and @Bill Sheboy for the kind help! I will next use some time to try the automation work-around ideas you mentioned, however this will be a bit above and beyond my usual current Jira level : ) 

Well, it's nice to learn new things along the way, i'll see how this pans out. And if this does not easily work out, we will most probably pivot to some more standard way of using the functionality in this programme- i reckon using "sprints" for up-front scheduling is not how one is supposed to use them anyway. 

In case I happen to make this work, I'll come back to post my resolution at some point. 

Best regards,
Ville