Need a JQL Query that shwos all bugs that had spent more than 45 min. in a specific status.

[Michael Böll]

Hi searching for an JQL query that find all bugs that spent more than 45 min in a specific status. Actually i only had a query that find the bugs which are actually in this status and older than 45min.

project = "xxx"  and createdDate <= -45m AND status = "xxx" 

But if a bug was changed after e.g. 60 min this query will not return it.  I need something like spent "45m in status xxx"

[Pablo Beltran · Accepted solution]

Hi @Michael Böll ,

If your Jira is not Cloud then the SQL+JQL Driver calculates all the time spent on every status on the ISSUESTATUSTRASITIONS table

Thus, your JQL would be:

issue in sql("select jql.issueid from jql join issuestatustransitions t on t.issueid = jql.issueid where query = 'project= xxx and type = Bug' and t.fromstatusid = yyy and intervaltime > 45*60")

This will get all issues of type Bug in the xxx project that spent more than 45*60 seconds in the yyy status id

Hope this helps,

Pablo (worker at the company developing SQL+JQL)

EDIT: From the 7.14 version interval has become a reserved word and the column name is now intervaltime

[Michael Böll]

Thanks alot I will test this.

[Casper Hjorth Christensen]

Hi @Pablo Beltran 

Is the app "SQL+JQL Driver" able to compare two fields?

I have a need to compare and show the following in a Dashboard

"DUE DATE" + 5d >= "RESOLITION DATE" ----> Dashboard gadget filter "not in breach"

"DUE DATE" + 5d <= "RESOLITION DATE" ----> Dashboard gadget filter "in breach"

[Pablo Beltran]

Absolutely:

SELECT 
   CASE WHEN 
            TIMESTAMPADD(DAY, 5, due(jql.issue)) >= resolved(jql.issue) 
        THEN 
            'Not in breach' 
        ELSE 
            'In breach' 
    END as "Breach"
FROM 
   jql 
WHERE 
    jql.query = ' <your JQL query here to limit the search scope> ' 

Since the SQL+JQL Driver app is essentially an H2 engine resolving Jira users' queries, it fully supports the

H2 SQL Grammar I.e CASE WHEN ... THEN ... ELSE ... END)

H2 SQL Functions I.e TIMESTAMPADD(... , ... , ...)

Very powerful indeed :)

[Casper Hjorth Christensen]

Amazing - Thanks

[Bill Sheboy]

Hi @Michael Böll 

How about this filter query which checks for any bug that has not changed any non-done status within the last 45 minutes?

project = "xxx"
 AND issuetype = Bug
 AND statusCategory IN ("To Do", "In Progress")
 AND NOT (status CHANGED AFTER -45m)
 ORDER BY Key

Best regards,

Bill

[Michael Böll]

Thanks for your tip.

With this query i get every change of the status. We have a self defined status for the bugs like: created, analyse, fixed, done.

I want check the bugs which spent more than 45 min in created. 

e.g. 

Bug 1 spent 44 min in created before it changed to analyse. There it spent 46 min before it changed to fixed. 

The query should not return this bug.

Bug 2 spent 46min in created before it changed directly to fixed. There it spent 1 min before it changed to done. 

The query should return this bug,

Only the first change of the status should be controlled, even there are more than one status changes.

[Bill Sheboy]

Hi @Michael Böll 

Thanks for clarifying.  Please consider replacing the statusCategory check in the query with a status check

project = "xxx"
 AND issuetype = Bug
 AND status = Created
 AND NOT (status CHANGED AFTER -45m)
 ORDER BY Key

For more information on searches like this, please see:

https://confluence.atlassian.com/jirasoftwarecloud/advanced-searching-764478330.html