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

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
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

211 views 1 18
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