How to retrieve all items in a given state between two dates

[Filipe]

I am trying to establish whether the number of bugs in my backlog is going up or down over time. I'm on a next-gen project on the cloud. And I need to build a JQL that I can use in my Python script. 

Goal

Retrieve all the bugs that were not in state Done between two dates. 

My query

project = pdn AND issuetype = bug AND status was NOT in ("Done") During ("2020-12-07", "2020-12-13")

Result (unexpected!)

The query retrieves ALL the bugs in the backlog regardless of what I put in the During clause (you can even put any future date, say in year 2023).

Cry for help

How do I retrieve *only* the bugs whose status were not done in between two dates?

[Kian Stack Mumo Systems]

@Filipe, 

This query works for me. I am able to use the during clause to filter out tickets that were not in this state during a specific period of time.

It looks like your query will get any bug in the project currently open, along with bugs that were not in the status Done during the period of time you specified.

You could also create a new board and update your filter to just show bugs, and then use the cumulative flow diagram to see whether the # is increasing or decreasing over time.

[Bill Sheboy]

Hi @Filipe 

Is this for a Next-Gen project, or do you have any Next-Gen projects in your instance?  If so...

We recently encountered a defect where Status evaluation with WAS does not evaluate correctly due to the way status was implemented for Next-Gen.  This defect does not impact all sites.  The work-around is to find the specific status ID for Done in your project and use that value in the JQL.  For example, if your Done status id was 12345, please use:

project = pdn
 AND issuetype = bug
 AND status WAS NOT IN (12345) During ("2020-12-07", "2020-12-13")

You may find your status ID values to confirm by asking your site admin or calling the REST API to see the full list (replace mySiteURL with your own):

mySiteURL/rest/api/3/status

Best regards,

Bill

[Filipe]

Hi Bill,

I tried exactly what you said without success. Have a look: 

project = pdn
AND issuetype = bug
AND status WAS NOT IN (10115) During ("2010-12-07", "2010-12-13")

I even narrowed down to dates before we started using Jira.

And here's how I established the ID of the column (using the URL you suggested):

{
  "self": "https://<mySiteURL>/rest/api/3/status/10115",
  "description": "",
  "iconUrl": "https://<mySiteURL>/",
  "name": "Done_DN",
  "untranslatedName": "Done_DN",
  "id": "10115",
  "statusCategory": {
    "self": "https://<mySiteURL>/rest/api/3/statuscategory/3",
    "id": 3,
    "key": "done",
    "colorName": "green",
    "name": "Done"
  },
  "scope": {
    "type": "PROJECT",
    "project": {
      "id": "10050"
    }
  }
},

The column "Done_DN" is unique across all my projects, so it must be the correct id.

Thanks,
Filipe

[Bill Sheboy]

Hi Filipe,

Two more things to try:

• Remove the project part of your query and see if it returns anything. That will rule out an issue with the status scope in (or not) for the project.
• Remove the During part of your query and see if it returns anything. That will rule out if the time range doesn't have any issues in it.

After those, I suggest finding a specific Bug in that project that you believe should be returned by the query, and then ask your site admin to submit a defect to Atlassian to get support involved: https://support.atlassian.com/contact/#/

Best regards,

Bill