Get status of issues on a particular date -SQL Server

Hi

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

Please suggest

2 answers

This widget could not be displayed.

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

This is giving errors on SQL server as follows:


Operand type clash: ntext is incompatible with numeric

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

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'>;

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.

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

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
...

This widget could not be displayed.

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

199 views 1 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you