SQL Query to get created vs solved issues

[Abner Isaac López Romero]

Hello,

I have a selfhosted JIRA server, and I am making a external dashboard, I am listing the status of the issues, and I want to show something like the data in the Created vs Solved issues in the Graphs section.

Can you provide me help with a SQL Query that can showme this info, only adding the proyect key or number?

Thanks.

[Abner Isaac López Romero · Accepted solution]

Nevermind, I already found out for myself

DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = GETDATE()
SET @Date2 = DATEADD(day,-30,@date1)

SELECT DATEADD(DAY,number,@Date2) Fecha
, (SELECT COUNT(*) FROM jiraissue INNER JOIN project ON project.ID = jiraissue.PROJECT WHERE project.pkey = 'SIPER' AND issuetype = 10100 AND CONVERT(DATE, CREATED) <= DATEADD(DAY,number,@Date2)) Abiertas
, (SELECT COUNT(*) FROM jiraissue INNER JOIN project ON project.ID = jiraissue.PROJECT WHERE project.pkey = 'SIPER' AND issuetype = 10100 AND CONVERT(DATE, RESOLUTIONDATE) <= DATEADD(DAY,number,@Date2)) Resueltas
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number,@Date2) <= @Date1

[Nic Brough -Adaptavist-]

This is not a good way to report on this.  The Jira database is not built for reporting and you will quickly find yourself building complex, expensive queries that are slow, probably inaccurate, and stress your systems.

Please, do this properly and ask Jira for the information over the REST interface.  Or better, inside Jira.

[Abner Isaac López Romero]

Thanks for your answer.