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

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