Sprint Start Date: JQL query

[Amir Malka]

Hello,

Can i extract sprint start date using JQL?

If not, is there another way this can be acomplished?

Many Thanks!

Amir

[Dawid Joshua _TechTime_]

Hi @Amir Malka,

Unfortunately, JQL doesn't offer this functionality. There is a feature request you might want to vote on: https://jira.atlassian.com/browse/JRACLOUD-72007 

There might be a much better way than the one I'm about to offer you, and it might miss your use case. 

Sprints are attached to a board. You will need the board ID, which you can get by opening the board and having a look at the URL:

In my case, the ID is 20. 

Then, you can use REST API to all sprints by navigating to the following URL:

https://<site_name>.atlassian.net/rest/agile/1.0/board/<board_ID>/sprint

That's what I get:

If you needed this information displayed somewhere, well, you'd need to write a script to get the startDate and do something with it. 

Cheers,
Dawid

[Nic Brough -Adaptavist-]

Welcome to the Atlassian Community!

No, JQL is for finding issues

You need to look to your reporting functions to find your sprint data

[Tim Mutlow]

Not an answer sadly as it doesn't appear to be possible but just to add my use case to the convo.

I'm trying to do the same thing as we use an automation to set the due date when it transitions to "In Progress" based on the size of the issue to help us stay on track.

My use case requiring the sprint start/end dates is simply to query for any issues with due dates that fall beyond the end of the current sprint.

I'd suggest sprint dates are absolutely within the scope of filtering for issues so should be included in JQL.

[Grant Mitchell]

Adding my use case (in case there's another way to achieve). I want to create a swimlane for stories added in sprint (interrupts), so if created_date>sprint_start put 'em in this swim lane so we can call out and discuss interrupts.

[Bill Sheboy]

Hi @Grant Mitchell -- Welcome to the Atlassian Community!

The solution you describe would not find all sprint, scope change issues: it would only find the ones created after the start of the sprint.  If one from the backlog was added, it would not appear.

One solution for this is to use an automation rule and an indicator (e.g., a label value).  For example:

• trigger: issue field changed for sprint
• smart value or JQL condition to check if the issue is now in the current sprint
• action: add a label, such as scope_added

The swim lane could use JQL to detect that label and raise visibility of the issues.

Kind regards,
Bill

[Bill Sheboy]

Hi @Amir Malka -- Welcome to the Atlassian Community!

As others have noted, that is not possible with JQL.

What problem are you trying to solve by getting the sprint start date in a query?  Knowing that may help the community to offer you suggestions to help.

Kind regards,
Bill

[George Yaacoub]

@Bill Sheboy  In my case:
I want to write a JQL query that will exclude issues that were marked as DONE in a previous sprint. Simple.
I don't want to include in my Active sprint board's DONE column issues that were marked as DONE previously.

[Nic Brough -Adaptavist-]

Your active spring board will not contain issues completed in previous sprints, you don't need to do anything to make that happen.

Your main question can be answered without looking at dates, try adding this to a copy of your board filter:

and ( Status not in (<list of status mapped into the done column>) and sprint not in openSprints() )

[George Yaacoub]

@Nic Brough -Adaptavist- here's how it goes in our team:
Issue of story type has 5 sub-tasks. 3 sub-tasks were marked as DONE in sprint 1, the same story is moved to sprint 2 with 2 remaining undone sub-tasks. 
In sprint 2's active board, as soon as we start the sprint 2, will show:
3 subtasks already marked as done, and 2 sub-tasks in the todo.
I am trying to hide those 3 subtasks already done in sprint 1. The only way I could do that, as of now, is to add to JQL a condition to hide issues with a resolution date that is prior to the START of the Active sprint.
what do you think?

[George Yaacoub]

In the case I described above, I don't think the below will work:
and ( Status not in (<list of status mapped into the done column>) and sprint not in openSprints() ) 
because the issue + all 5 subtasks in my example, will contain sprint 1 + sprint 2 in their list of sprints they belong to. so "sprint not in openSprints()" will exclude completely the story issue

[Nic Brough -Adaptavist-]

No, you are absolutely right.

This isn't going to work the way you want it to.  Sub-tasks are not sprint items, they are fragments of issues which are sprint items.

They only appear on a board because the board understands that they are part of items that it is being asked to show.

You can't hide them from a board selection, because they may be part of something that is on the board.  Their status when you are doing that is irrelevant - they're either "to do" or "in progress" because their containing story is, or they should be "done" because their container is also done.

You can't work with partial views of a sprint item (story, container issue, etc), you need to be able to see the whole thing, so your sub-tasks are always going to appear in full on the board by default.  They have to be on the board for you to be able to work with them and their containers.

However, there is one thing you can do with boards.

Quick filters are intended for drilling down into your board data, and, in my opinion, the two most useful quick filters should be built into Jira:

If board could include sub-tasks, then we should automatically have at built-in quick-filter for "show only base level issues" (alternatively, call it "hide sub-tasks").  For a couple more brownie points, add "show only sub-tasks" alongside it.  I've lost count of the times I've copied and pasted "issuetype in standardIssueTypes()" into a new quick filter for a board.

[George Yaacoub]

Thank you, Nic for sharing your thoughts. Your point of view is valid, I will be keeping the old sprint DONE subtasks visible.

[Narsha Bac]

Hello,

I have a similar use case where I need to know if due date for an issue is within the start date and end date of a sprint (sprint in which the issue is).  Is there a way to know this please? I need this info for my automation.

For e.g, if the issue's due date is not within the start date and end date of the sprint (sprint in which the issue is), then the due date will be updated to NULL.

Can I have a solution for this?

Thanks.

[Bill Sheboy]

Hi @Narsha Bac 

Those smart values / attributes of the sprint are available in automation for the sprints the issue is assigned to.  And those may then be compared with conditions or the date / time rule functions. 

For sprints not assigned and which you want to compare, those would need to be read using the REST API functions and the Send Web Request action.

If that does not help, I recommend creating a new question, including images of your complete rule, the relevant action details, and of the audit log details showing the rule execution, explaining what does not work as you expect.  This is an older thread, and creating a new question will help more community members see it to offer suggestions.

Kind regards,
Bill