Get Total Spent Time On Sql Query for Parent Task

Question

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..

PD Sheehan · Answer

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:

PRABHURAM K R · Answer

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

PD Sheehan · Answer

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