select newvalue,issueid,created from jiraschema.changegroup,jiraschema.changeitem where changegroup.created < '02/05/2013' AND changeitem.GROUPID = changegroup.ID )as CH1 left outer join ( select newvalue,issueid,created from jiraschema.changegroup,jiraschema.changeitem where changegroup.created < '02/05/2013' AND changeitem.GROUPID = changegroup.ID ) as CH2 on (CH1.issueid = CH2.issueid and CH1.created < CH2.created) where CH2.issueid is null
This is what I'm using finding the max created date from changegroup table from the given date and getting the newvalue . THis gives some results , but missses out on issues which are created and no activity has been done as yet . Those will not be listed in this table and th eonly entry I find is in the assignee field with the new value as the username for the user .
Hi Pushparaj,
I believe you can use the following query to help you achieve that:
SELECT JI.pkey, STEP.STEP_ID FROM (SELECT STEP_ID, ENTRY_ID FROM OS_CURRENTSTEP WHERE OS_CURRENTSTEP.START_DATE < '<your date>' UNION SELECT STEP_ID, ENTRY_ID FROM OS_HISTORYSTEP WHERE OS_HISTORYSTEP.START_DATE < '<your date>' AND OS_HISTORYSTEP.FINISH_DATE > '<your date>' ) 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 = <proj_id>;
This query retrieves the status of all issues on a give date in a give project. You can find more query examples at: Example SQL Queries for JIRA
Hope this helps!
Cheers,
Clarissa.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.