SQL Query

[nath]

Hi,

Need help in writing a SQL query to pull list of all Projects with Issues that are linked to EPICs in one particular project in JIRA

Thanks

[ahmet tetik]

SELECT CREATED CREATEDATE,P.PKEY+'-'+convert(nvarchar,issuenum)+P.PKEY+'-'+CONVERT(NVARCHAR,issuenum) [KEY],
c.display_name Createtor,r.display_name Reporter,a.display_name Assigne,
ik.display_name Ilgilisi,ok.display_name Onaylayan,tk.display_name TestEdecek,case when cf.CUSTOMFIELD=10144 then DATEVALUE end TahminiBaslangic,
case when cf.CUSTOMFIELD=10145 then DATEVALUE end TahminiBitis, I.DueDate BitisTarihi,iis.ICONURL Durumu,w.Saat CalismaSuresi,PR.ICONURL as OncelikDurumu,
Summary Baslik,i.DESCRIPTION Ozet,pr.pname ODurumValue, iis.pname SDurumValue
FROM jiraissue I
JOIN PROJECT P ON I.PROJECT=P.ID AND P.ID IN (10104,10200,10201)
join cwd_user c on c.user_name=i.creator and c.active=1
left join cwd_user r on r.user_name=i.reporter and r.active=1
left join cwd_user a on a.user_name=i.assignee and r.active=1
LEFT JOIN customfieldvalue cf on cf.ISSUE=I.ID AND CUSTOMFIELD IN (10111,10144,10145,10204,10203)
left join cwd_user ik on ik.user_name=(case when cf.CUSTOMFIELD=10111 then STRINGVALUE end )
left join cwd_user ok on ok.user_name=(case when cf.CUSTOMFIELD=10204 then STRINGVALUE end )
left join cwd_user tk on ok.user_name=(case when cf.CUSTOMFIELD=10203 then STRINGVALUE end )
join issuestatus iis on iis.ID=I.issuestatus
left join (select issueid,convert(int,(convert(float,sum(timeworked)/1500))) Saat FROM worklog GROUP BY issueid) w on w.issueid=i.ID
join priority pr on pr.ID=i.PRIORITY

[nath]

Thanks for the quick replay. 

I just need list of all Projects that are linked to EPICs in other project in JIRA