How to retrieve status of a particular issue through SQL on a particular date

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 28, 2012

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

3 answers

1 accepted

0 votes
Answer accepted
Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2012

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 statuses
SELECT TBL1.pkey, TBL1.issueid, TBL1.NEWVALUE AS ValueID, TBL1.NEWSTRING As Value,TBL1.CREATED As StartTime, TBL2.FINISHED AS EndTime
FROM
-- 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 TBL2
WHERE TBL1.NEWVALUE = TBL2.OLDVALUE
AND TBL1.issueid = TBL2.issueid
AND TBL1.pkey = TBL2.pkey
AND 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 TBL4
WHERE
--pkey = @issue AND
--Value = 'Research' AND
CONVERT(DATE, StartTime) <= '2012-04-02'
AND CONVERT(DATE, EndTime) >= '2012-04-02'
ORDER BY pkey, StartTime

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 4, 2012

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

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 5, 2012

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.

2 votes
Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 28, 2012

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 < @date
UNION 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.ID
UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
FROM jiraissue) As VALID,jiraissue as JI
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
AND JI.project IN (select ID FROM project WHERE pname = @project)
AND JI.pkey = @issue

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 28, 2012

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!

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 28, 2012

Oh, I tested on Jira 5.1.5, so I am expecting it should work 5.0.5.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 28, 2012

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

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 2, 2012

Hi Norman,

Thanks for the query.

To get an issuestatus ('RBT-5543') on date 30th july 2012.

I have given the query as:

SELECT JI.pkey, (SELECT pname FROM issuestatus WHERE SEQUENCE = STEP.STEP_ID),STEP.STEP_ID FROM (SELECT STEP_ID, ENTRY_ID FROM OS_CURRENTSTEP WHERE OS_CURRENTSTEP.START_DATE &lt;'2012-07-30' 

UNION

SELECT STEP_ID, ENTRY_ID FROM OS_HISTORYSTEP WHERE OS_HISTORYSTEP.START_DATE &lt; '2012-07-30' AND OS_HISTORYSTEP.FINISH_DATE &gt;'2012-07-30') AS STEP,

(SELECT changeitem.OLDVALUE 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 WHERE STEP.ENTRY_ID = VALID.VAL AND VALID.ISSID = JI.id AND JI.project IN (SELECT ID FROM project WHERE pname = 'RBT') 

AND JI.pkey ='RBT-5543';

Please find the Output:

Actual output when checked history of that issue is "Testing" (custom)status.

Please let me know am i missing anything in the query.

Thanks & Regards

Kapil

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 2, 2012

It is hard to say without looking at the data and results of the individual subqueries. So lets start simple.

You got step_id of 2

So what does "select * from issuestatus " return?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 2, 2012

Um, "Testing" doesn't exist...

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 2, 2012

Hi Norman,

Please find the result of select * from issuestatus:

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 2, 2012

Hi Nic,

There are in all 66 statuses , Testing does exist with id='10055' & sequence no:62. not shown in screenshot

-Kapil

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 2, 2012

The point of Norman's question here was to check that "Testing" is on the list, so posting something that doesn't show it really misses the whole point.

As a generalisation, when you have a complex SQL query that is returning something you don't expect (in this case, it's definitely not giving you anything useful), then you need to debug it. Break it down into more simple clauses and check that they work individually.

Can you check the output of each SELECT segment separately? Do all of them return what you expect them to? If you're not sure what to expect, then ask, and maybe include the full output (as you're working from Norman's code and you might not understand what it's doing)

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 2, 2012

ummmm, besides no testing label there is no step identifier 2 either. Not the direction I thought I was going to follow but explains the null value. I am fairly confident the overall query it correct.

hmmm. Now we need to see the 1) workflow that shows the steps defined hopefully with the ids and

2) some raw history records to make sure that a step id 2 does exist there

Feels like an imported or modified workflow with some missing pieces.

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 2, 2012

Grumble,

Ok, 1) Please check the history steps contains a step id of 2 in it list.

If not, then maybe you need conversions to convert a text field to a number to get old value out

2) Which database are you using by the way?

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 2, 2012

Nic,

First, thank you for your time and parcipating on this issue. I think maybe the issue might be related to the slight differences between step names and status names. In my case they are the same name with the same ids. I do not remember which table might have the step names. Do you remember?

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2012

Please verify that the following query:

1) the data values you want are returned by this query
2) the data values OLDVALUE and NEWVALUE all exist in issuestatus table

declare @issue nvarchar(20) = 'EA-142'

SELECT JI.pkey, JI.CREATED AS IssueCreated, (select pname FROM [JiraProduction].[JiraSchema].[issuestatus] WHERE ID = issuestatus) as CurrentStatus, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING,CG.CREATED
FROM [JiraProduction].[JiraSchema].[changeitem] AS CI ,
[JiraProduction].[JiraSchema].[changegroup] CG,
[JiraProduction].[JiraSchema].[jiraissue] JI
WHERE CI.groupid = CG.ID
AND CI.FIELD = 'status'
AND CG.issueid = JI.ID
AND JI.pkey = @issue
ORDER BY JI.pkey, CREATED

I am trying to see if moving away from workflow steps to issue statuses returns the data you are looking for as well.

If everyting checks out then the query can be broken up to give you date ranges

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2012

Hi Norman,

I am using Mysql 5.5.

Please find the output:

Thanks & Regards

Kapil

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2012

thanks for the output but you need to tell me if the values are what you looking for, since you are only giving partial data results.

1) So is the key 10072 in the issuestatus table and does it correspond to "Re tested".

2) Are these the statuses you are looking for?

3) You talked about a testing status. Is "Re tested" your testing status?

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2012

Hi Norman,

1) So is the key 10072 in the issuestatus table and does it correspond to "Re tested".

-->yes it corresponds to Retested status

2) Are these the statuses you are looking for?

--> I am looking for status of issues/ issue at a particular date.

3) You talked about a testing status. Is "Re tested" your testing status?

-->Yes

Thanks & Regards

Kapil

Kapil Bolli
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2012

Hi Norman,

So is the key 10072 in the issuestatus table and does it correspond to "Re tested".

-->> yes .

2) Are these the statuses you are looking for?

-->> I am looking for status of an issue/isssues at a particular date.

3) You talked about a testing status. Is "Re tested" your testing status?

--->>Yes, it is Re-tested & not testing.

Thanks & regards

Kapil

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 3, 2012

The answer query above does work, but could not quickly find the step names in the database. I am suspecting it might be some xml file and I did not want to create selection function since that function would need to tailored.

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 28, 2012

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"

Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 5, 2012

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.

Suggest an answer

Log in or Sign up to answer