Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Time in Source Status JQL

Without using plugins, what is the best way to pull the time in each status?

I'm trying to pull the time from (Open to In Progress) and (In Progress to Ready for Testing) in the TRANSITION tab. Unfortunately, I cannot figure out how to use JQL to pull the total of these numbers (in a handful of defects). 

 

1 answer

Is this what you are looking for;

select 
p.pkey||'-' ||i.issuenum as jira_id,
s.pname last_status,
row_number() over(partition by st.issueid order by st.groupid) status_order,
st.status S_from,
st.new_status S_to,
nvl(st.date_start,i.created) as start_time,
st.date_end as stop_time,
round (st.date_end - nvl(st.date_start,i.created),3) stop_start
from jirasd.jiraissue i,
jirasd.project p,
jirasd.issuestatus s,
(select cg.issueid, cg.id as groupid, to_char(ci.newstring) new_status, to_char(ci.oldstring) status,
LAG(cg.created) OVER (partition by cg.issueid ORDER BY cg.id) date_start,
LAG(cg.id) OVER (partition by cg.issueid ORDER BY cg.created) prev_groupid,
cg.created date_end
from jirasd.changeitem ci,
jirasd.changegroup cg
where ci.field = 'status'
and ci.groupid = cg.id
) st
where p.id = i.project
and i.issuestatus = s.id
and st.issueid = i.id

Thank you, the solution was very helpful.

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you