Get status of issues on a particular date -SQL Server

[Pushparaj Bhaskaran]

Hi

I need to get the status of all issues on a particular date using SQL query .

Please suggest

[Frédéric Tardieu]

Hi Pushparaj,

If you want to get this in a visual way without writting any SQL, you can use Graphit plugin with its "status" view. It will show you statuses evolution of one issue or all issues of any project, over dates or over vesions. You can also display in the same view who authored the status changes.

Best regards,

Frédéric

[Norman Abramovitz]

Here is a query that you can modify to get what you want. It gets status counts on a particular date.

https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-FindStatuscountsforaProjectonagivendate

[Pushparaj Bhaskaran]

This is giving errors on SQL server as follows:

Operand type clash: ntext is incompatible with numeric

[Norman Abramovitz]

This query is closer to what you need.

https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-FindStatusesofallissuesinaprojectonagivendate

[Norman Abramovitz]

You have to adjust the query for the database you are using since these queries are on MySql. You need a cast operation.

[Norman Abramovitz]

Here is the query for 6.1 and greater. The project key defintions changed. I put in the missing cast for you. If you have an earlier version then JI.pkey will work for you.

SELECT PK.PROJECT_KEY,JI.issuenum, STEP.STEP_ID, STEP.STATUS
FROM (SELECT STEP_ID, ENTRY_ID,STATUS

FROM OS_CURRENTSTEP

WHERE OS_CURRENTSTEP.START_DATE< '<date>'

UNION SELECT STEP_ID, ENTRY_ID,STATUS

FROM OS_HISTORYSTEP

WHERE OS_HISTORYSTEP.START_DATE<'<date>'

AND OS_HISTORYSTEP.FINISH_DATE >'<date>')As STEP,

(SELECT CAST(CAST(changeitem.OLDVALUE AS nvarchar(32)) AS INT)AS VAL, changegroup.ISSUEID AS ISSID

FROM changegroup,changeitem

WHERE changeitem.FIELD ='Workflow'

AND changeitem.GROUPID = changegroup.ID

UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID

FROM jiraissue)As VALID,

jiraissue as JI,

PROJECT_KEY as pk

WHERE STEP.ENTRY_ID = VALID.VAL

AND VALID.ISSID = JI.id

and pk.PROJECT_ID = JI.PROJECT

AND JI.project = '<projectid'>;

[Pushparaj Bhaskaran]

THanks , I modified as follows for 5.1 version JIRA as that is the one I'm running on like this

SELECT JI.pkey, STEP.STEP_ID, STEP.STATUS
FROM (SELECT STEP_ID, ENTRY_ID,STATUS
FROM JIRA.jiraschema.OS_CURRENTSTEP
WHERE OS_CURRENTSTEP.START_DATE< '03-21-2014'
UNION
SELECT STEP_ID, ENTRY_ID,STATUS
FROM JIRA.jiraschema.OS_HISTORYSTEP
WHERE OS_HISTORYSTEP.START_DATE<'03-21-2014'
AND OS_HISTORYSTEP.FINISH_DATE >'03-21-2014')As STEP,
(SELECT CAST(CAST(changeitem.OLDVALUE AS nvarchar(32))AS INT)AS VAL, changegroup.ISSUEID AS ISSID
FROM JIRA.jiraschema.changegroup,JIRA.jiraschema.changeitem
WHERE changeitem.FIELD ='Workflow'
AND changeitem.GROUPID = changegroup.ID
UNION
SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
FROM
JIRA.jiraschema.jiraissue)As VALID,
JIRA.jiraschema.jiraissue as JI
--pkey as pk
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
--and pk.PROJECT_ID = JI.PROJECT
AND JI.project = 10502;

THe output that I get for status column are lik e "Done" Not DOne" and "open". Actual name of the
status is what I like to be displayed like resolved,ready to test etc.

[Pushparaj Bhaskaran]

@Norman, can you please explain what it need to get me the status names please

[Norman Abramovitz]

You need to take the step id and look it up in the issuestatus table.

Change your top level select list to something like this

SELECT JI.pkey, STEP.STEP_ID, (select pname FROM issuestatus where SEQUENCE = STEP.STEP_ID)AS Status
...