Hi ,
I am using Jira 5.0.5,
i want to retrieve status of an issue through SQl on a particular date.
Also list of issues in 'X' state on 'Y' date through SQl.
Database is Mysql.
Thanks
Kapil
Given that you are unclear on SQL and you are unclear what you really mean by the status on a particular date, (ie
1) the first status for date for a paritcular issue, 2) or all the statuses changed on that date for a particular issue 3) or just the last status on that date for a particular issue
I came up with a query that gives you a start and finsh dates, so you can do things with simple compares.
There is a much simpler query with just start dates and then you use order by start date and select the top row depending on your date definition.
So here is the longer query. I needed the converts given the choice that Altassian chose for the column types.
Also the lines with -- are comments so those will probably need to be changed to your comment convention
The conversion on the dates at the end removes the time portion and that might need to be changed due to your database date functions.
Tested On Sql Server 2008 with a small number of issues (< 200)
NOTE Updated Query to deal with the case of no status change items.
declare @issue varchar(20) = 'EA-170'
SELECT * FROM -- last status (use current time for EndTime)(SELECT JI.pkey, CG.issueid, CONVERT(nvarchar(max), CI.NEWVALUE) AS ValueID, CONVERT(nvarchar(max),CI.NEWSTRING) AS Value,-- Replace GETDATE() with your database function for current time CG.CREATED AS StartTime, GETDATE() AS EndTime FROM changeitem AS CI , changegroup CG, jiraissue JI WHERE CI.groupid = CG.ID AND CI.FIELD = 'status' AND CG.issueid = JI.ID AND CG.CREATED = (select MAX(CG2.CREATED) FROM changegroup CG2, changeitem CI2 WHERE CG2.issueid = CG.issueid AND CI2.FIELD = 'status' AND CG2.ID = CI2.groupid) UNION-- first status (use issue create date for start time if status was previous modified)SELECT JI.pkey, CG.issueid, CONVERT(nvarchar(max), CI.OLDVALUE) AS ValueID, CONVERT(nvarchar(max),CI.OLDSTRING) AS Value, JI.CREATED AS StartTime, CG.CREATED AS EndTime FROM changeitem AS CI , changegroup CG, jiraissue JI WHERE CI.groupid = CG.ID AND CI.FIELD = 'status' AND CG.issueid = JI.ID AND CG.CREATED = (select MIN(CG2.CREATED) FROM changegroup CG2, changeitem CI2 WHERE CG2.issueid = CG.issueid AND CI2.FIELD = 'status' AND CG2.ID = CI2.groupid)UNION-- first status (use issue create date for start time -- and current time if status was never modified)SELECT JI.pkey, JI.ID AS issueid, JI.issuestatus AS ValueID, (SELECT pname FROM issuestatus AS JIS WHERE JIS.ID = JI.issuestatus) AS Value, JI.CREATED AS StartTime, GETDATE() AS EndTime FROM jiraissue JI WHERE NOT EXISTS(SELECT 1 FROM changeitem AS CI, changegroup CG WHERE CI.groupid = CG.ID AND CI.FIELD = 'status' AND CG.issueid = JI.ID)UNION-- midde statusesSELECT TBL1.pkey, TBL1.issueid, TBL1.NEWVALUE AS ValueID, TBL1.NEWSTRING As Value,TBL1.CREATED As StartTime, TBL2.FINISHED AS EndTimeFROM-- middle status with its starttime(SELECT JI.pkey, CG.issueid, CONVERT(nvarchar(max),CI.NEWVALUE) AS NEWVALUE, CONVERT(nvarchar(max),CI.NEWSTRING) AS NEWSTRING,CG.CREATED FROM changeitem AS CI , changegroup CG, jiraissue JI WHERE CI.groupid = CG.ID AND CI.FIELD = 'status' AND CG.issueid = JI.ID ) AS TBL1, -- middle status to get the end time -- Match TBl1 newvalue to TBL2 oldvalue -- and then make sure the TBL2 date is newer than TBL1 date -- and make sure the earliest match is used if duplicate statuses exist(SELECT JI.pkey, CG.issueid, CONVERT(nvarchar(max),CI.OLDVALUE) AS OLDVALUE, CONVERT(nvarchar(max),CI.OLDSTRING) AS OLDSTRING,CG.CREATED AS FINISHED FROM changeitem AS CI , changegroup CG , jiraissue JI WHERE CI.groupid = CG.ID AND CI.FIELD = 'status' AND CG.issueid = JI.ID ) AS TBL2WHERE TBL1.NEWVALUE = TBL2.OLDVALUE AND TBL1.issueid = TBL2.issueid AND TBL1.pkey = TBL2.pkeyAND TBL2.FINISHED = (select MIN(CG2.CREATED) FROM changeitem AS CI2, changegroup CG2 WHERE CI2.groupid = CG2.ID AND CI2.FIELD = 'status' AND CG2.issueid = TBL2.issueid AND CG2.CREATED > TBL1.CREATED AND CONVERT(nvarchar(max),CI2.OLDVALUE) = TBL2.OLDVALUE)) AS TBL4WHERE --pkey = @issue AND--Value = 'Research' ANDCONVERT(DATE, StartTime) <= '2012-04-02'AND CONVERT(DATE, EndTime) >= '2012-04-02'ORDER BY pkey, StartTime
I can't give you the exact SQL, my SQL skills are pretty basic. I can tell you where to look though.
The tables you need are changeitem, changegroup and jiraissue.
If the status of an issue has never changed, then read Jiraissue, because it has the current status (you'll need to read the status table for the name of the status - jiraissue holds a key, not the label)
For a historical status, you need to read through changeitem and changegroup. Each change to an issue is added to changegroup as a line with the author and the date/time it happens. Each part of a change is written to changeitem as a from/to line (with the id of the changegroup), so you're looking for "status changed from X to Y" in there.
As I said, my SQL is a bit too weak to know how to do that. My instinct is to start from
select * from changeitem join changegroup on changeitem.group = changegroup.id where field = status and issueid = xxxxxx
I'm not 100% sure of the column names, I'm working from memory. xxxxx is the Issue ID, not the Key - you'll find that on Jiraissue
That will get you a list of all the changes to an issue, and that's where my SQL falls down - I don't know how to say "date X falls before change Z and after change Y, so use the TO-Status from change Y"
This works for me on Sql Server 2008
declare @date datetime = '2012-9-4'declare @project nvarchar(20) = 'eAgents'declare @issue nvarchar(20) = 'EA-142'SELECT JI.pkey, (SELECT pname FROM issuestatus where SEQUENCE = STEP.STEP_ID)FROM (SELECT STEP_ID, ENTRY_ID FROM OS_CURRENTSTEP WHERE OS_CURRENTSTEP.START_DATE < @dateUNION SELECT STEP_ID, ENTRY_ID FROM OS_HISTORYSTEP WHERE OS_HISTORYSTEP.START_DATE < @date AND OS_HISTORYSTEP.FINISH_DATE > @date ) As STEP,(SELECT CONVERT(int, CONVERT(nvarchar,changeitem.OLDVALUE)) AS VAL, changegroup.ISSUEID AS ISSID FROM changegroup, changeitem WHERE changeitem.FIELD = 'Workflow' AND changeitem.GROUPID = changegroup.IDUNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID FROM jiraissue) As VALID,jiraissue as JIWHERE STEP.ENTRY_ID = VALID.VALAND VALID.ISSID = JI.idAND JI.project IN (select ID FROM project WHERE pname = @project) AND JI.pkey = @issue
Remove "AND JI.pkey = @issue" to see all issues on a particular date
For queries in a particular status add the following clause at the end of the query
Add "AND STEP.STEP_ID = (SELECT SEQUENCE FROM issuestatus where pname = @step)"
and declare the @step variable with the step name
Just remember NEVER update the database unless Jira is shut down completely!
Oh, I tested on Jira 5.1.5, so I am expecting it should work 5.0.5.
That'll work on Jira 4.x as well as all the way up to 5.1
I wanted to comment for clarity - both the os_workflow (Norman) and the change-history (me) methods of finding a previous status are valid - this is one of the few places where Jira effectively duplicates information (albeit in very different data structures). You can use whichever is easier/better for you.(And yes, never amend a Jira database...)
Hi Norman,
Thanks for all your SQL queries.
I am somewhat confused with SQl's during the whole conversation.
Now i am preety clear what i am looking for:
Get the count of issues in a "SUBMITTED" status on a particular date in a project.
Details: id=10078 ,Sequence=85,pname=Submitted in Issuestatus table.
Also, after checking the history of issue found that:workflow associated with issue is changed.
Will that create a problem in fetching results?
Thanks.
-Kapil
Thanks Nick, I corrected my query to deal with the case where there is no status records. That is one of the differences from the workflow step method.
Hi Kapil,
I updated the second query since I was missing one case that Nic's answer reminded me to check for.
There are multiple ways to get the count. One simple way is to change the "select * from" on the first line to select count(*) from. You can also use a group by clause.
If you set the status value to 'SUBMITTED' in the where clause at the bottom of the query, you will get a row even if the the status was changed multiple times on the same day.
If you want only the last status for the day, then wrap my query in another query and allow my query to select the latest status by date only and the new outer query restricts by the status value.
So I suggest get a new copy of my second query, update the areas where you need to make it work with mysql and run it a few times with different combinations of where clauses as noted at the bottom the query.
It looks like you're new here. Sign in or register to get started.
