i would like to have a SQL query wherein I can get the epic name of a story.
The database architecture 'n schema appears to be more confusing.This doesn't even describe much of details (https://developer.atlassian.com/jiradev/jira-platform/jira-architecture/database-schema#Databaseschema-Customfields)
Likewise - I do have tables customfield and customfieldvalue from where I can have the required 'stringvalue' as epic name but id and issue column makes more confusion as customfieldvalue.id is from nowhere to generate?
Run this query and replace the 10111 value to the relevant custom field value of your JIRA instance:
select p.pkey, ji.issuenum, cfv.STRINGVALUE from jiraissue ji join project p on ji.PROJECT = p.ID join customfieldvalue cfv on ji.ID = cfv.ISSUE where cfv.CUSTOMFIELD = 10111;
Output will contain the issue key and the custom field value.
@Noam Dahan thanks that works - but it returns epic issuetype list. What if I want to pass a pkey and issuenum=1836 (1836 will be key for story issuetype)?
select p.pkey, ji.issuenum, cfv.STRINGVALUE,ist.pname from jiraissue ji join project p on ji.PROJECT = p.ID join customfieldvalue cfv on ji.ID = cfv.ISSUE join issuetype ist on ji.issuetype = ist.id where cfv.CUSTOMFIELD = 10004 and p.pkey='ATPCWS' and ji.issuenum=1836
Query you provided worked; but output was only with EPIC issue type - wherein I was looking for a STORY ID to be passed and get the epic link 'name' (thats stringvalue). Your code really helped
I ended up creating the below one:
select ji.issuenum AS Epic_Key, cfv.STRINGVALUE AS Epic_Name from jiraissue ji join customfieldvalue cfv on ji.ID = cfv.ISSUE join issuetype ist on ji.issuetype = ist.id where cfv.CUSTOMFIELD = 10004 and ji.issuenum=( select issuenum from jiraissue where id=( select islk.source from jiraissue ji join issuelink islk on ji.id=islk.source join issuelinktype ilt on islk.linktype=ilt.id join jiraissue c on islk.destination = c.id where ilt.linkname='Epic-Story Link' and c.id=(select jre.id from jiraissue jre, project prj where jre.project=prj.id and prj.pkey='ATPCWS' and jre.issuenum=1836)) )
SELECT p.pkey || '-' || ji.issuenum AS issueid,cfv.stringvalue AS epicname FROM jiraissue ji LEFT JOIN project p ON p.id=ji.project LEFT JOIN customfieldvalue cfv ON cfv.customfield=(SELECT id FROM customfield WHERE cfname LIKE 'Epic Name') AND cfv.issue=ji.id WHERE ji.issuetype=(SELECT id FROM issuetype WHERE pname LIKE 'Epic') AND (cfv.stringvalue IS NULL OR cfv.stringvalue = '') ORDER BY p.pkey, ji.issuenum ;
Although this should never happen as the Epic Name field is required.
I was able to grab the Epic name in jira through looking at the issuelinktype table to see what id is the epic link set to.
Then I looked at the issuelink table and filtered that to only show the epic link type.
I did a left join to join based on the source(epic issuenum) and destination(task issuenum)
,el.summary AS "Epic Name"
from jiraissue as j
left join (select
left join jiraissue
on (issuelink.source = jiraissue.id)
and jiraissue.id is not null
) as el on (el.destination = j.id)
-- This will print the jira issue number ,epic name and time spent by any author
-- The query is time bound
-- mainly focused at grouping the time logged by any individual user
-- in relation to all the tasks that belong to an epic
-- give it a shot ,the source and the destination in the issuelink table help connect any
-- task to the epic it is linked to
select p.pkey || '-' || ji.issuenum AS Task_Number,(select summary from jiraadmin.JIRAISSUE where id=il.source ) as epic_name ,ji.summary Task_Name,ji.timespent/60 TIMESPENT_IN_MINUTES
from jiraadmin.JIRAISSUE ji,jiraadmin.PROJECT p ,jiraadmin.issuelink il
ji.id in (select issueid from jiraadmin.WORKLOG where author='<author name>' and trunc(created)>to_date('4/1/2019','mm,dd,yyyy'))
and ji.ID = il.destination(+)
order by epic_name desc
I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...
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