Community
Q&A
Jira
Questions
Get Total Spent Time On Sql Query for Parent Task

Get Total Spent Time On Sql Query for Parent Task

Hi, I want to know how to get the Parent Task Jira's Total Time Spent which is divided By Sub Task on Worklog's.. Any One Please help With Table name or Function That are Being used to get the Time Spent for Parent Task.. Please I need SQL Query for that..

2 answers

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

0 votes

But JIRA version is 7.4 which doesnt has a Table JIRAISSUE,Subtask Tables... Could you please help me with this..

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

There is only the jiraissue table.

But since I'm connecting jiraissue to another instance of jiraissue, I used an alias for the second table so that I know that this is the one I will use for subtask-level information.

For example, very simplified, without any linkage, here is selects from 2 tables (don't run this on a real db, especially if you have lots of issues).

SELECT
   parentIssue.*,
   subtaks.*
FROM
   jiraissue parentIssue,
   jiraissue subtask

Here is what my earlier query looks like in a graphical query builder tool:

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

0 votes

Each jiraissue record in the DB has its own "timespent" value. This will not necessarily match the sum of all associated jiraworklog entries for a given issue.

Linking between parent issue and subtask is done via the issuelink table where linktype = 10000 (or consult issuelinktype to make sure you get the correct id)

If you want to sum all the timespent from subtasks, you can try a SQL query like this:

SELECT
  project.pkey,
  jiraissue.issuenum,
  Sum(subtasks.TIMESPENT) AS Sum_TIMESPENT
FROM
  jiraissue
  INNER JOIN project ON project.ID = jiraissue.PROJECT
  LEFT JOIN issuelink ON issuelink.SOURCE = jiraissue.ID
  LEFT JOIN jiraissue subtasks ON subtasks.ID = issuelink.DESTINATION
WHERE
  project.pkey = 'JSP' AND
  issuelink.LINKTYPE = 10000
GROUP BY
  project.pkey,
  jiraissue.issuenum,
  issuelink.LINKTYPE

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Forums

Q&A

Community resources

Support

Top groups

Community resources

Support

Learn

Community resources

Support

Events

Community resources

Support

Get Total Spent Time On Sql Query for Parent Task

2 answers

Comments for this post are closed

Was this helpful?

Thanks!

TAGS

Community showcase

Atlassian Community Events