Required query user who has created last issue in his project with date and time

[srrekanth]

Hi ,

Is there any query for getting last created issue in his project ?i need this information for all projects.

[Udo Brand]

Here the query for last reporter per project.

SELECT i.created last_create_date, p.pname, i.reporter
FROM  project p, jiraissue i,
(SELECT max(pkey) maxpkey, project FROM jiraissue GROUP BY project) m
WHERE i.project = p.id 
AND maxpkey = i.pkey;

[MatthewC]

I'd start here

https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-ReturnOutdatedProjectsFromaSpecificDate

The query wil lgive you all old projects

SELECT DISTINCT pname
FROM project WHERE id NOT IN (SELECT project FROM jiraissue WHERE (created > '2011-07-01 00:00:00' AND updated > '2011-07-01 00:00:00'));

We also use the {run} and {sql} macros to run a report of old projects from Confluece. It takes a number which is the number of months old a project has to be to turn up on the report

{run:heading=Empty Projects Report|prompt=Find Empty & Old Projects|replace=numMonths::Months Ago:}


{sql:dataSource=jirareader|output=wiki|showSql=true|macros=true|columnLabel=true|showsql=true}
Select jira.project.pname,
       CASE WHEN tab1.IssueCounter is null then 0 else tab1.IssueCounter END as 'Issue Counter',
       tab1.LastUpdate as 'Last Update', LEAD as 'Project Lead'
from jira.project
Left join
(Select PROJECT,count(*) as 'IssueCounter',max(updated) as 'LastUpdate'
 from jira.jiraissue group by PROJECT) tab1
ON jira.project.ID=tab1.project
where tab1.LastUpdate < DATEADD(Month, $numMonths, getdate()) AND tab1.LastUpdate is not null
order by tab1.lastupdate asc
{sql}


{run}

I'm sure it can be adapted to add who the last updater was

[MatthewC]

p.s. that was for MS-SQL so you will need to change the date functions to ther MySQL equivalent

[MatthewC]

there's something not right with the last query, will get back to you with a fix

[MatthewC]

no, it's fine! I just forgot, you have to put the number in as a negative due to the quirks of MS-SQL

[srrekanth]

Hi Mathew,

for getting active and inactive projects for last six months we use the below is the below query is correct or wrong

SELECT project.pname, COUNT(Jiraissue.pkey) AS Kount

FROM Jiraissue RIGHT OUTER JOIN

project ON Jiraissue.PROJECT = project.ID

AND Jiraissue.created > '2012-08-007'

GROUP BY project.pname

ORDER BY project.pname,Kount

can you please little bit clear for last updated or last created issue in every project MYSQL query.

[srrekanth]

Hi Mathew,

please share the MYSQL query who last updated or created issue in thier project and i want this information for all the projects in jira.

[MatthewC]

Hi Kanthu,

It's not a report I have to hand as we don't use it. The only way to get the last updated person is that you'd have to search through the change history log as the last updater isn't stored in the jiraissues table. You'll only find assignee and reporter there, and reporter may, or may not be, the person who created it.

[srrekanth]

Hi Matthew,

I required a query in such a away that it should give active/inactive projects and last issue created date or updated for last 6 months.

I am not getting any thing the query which you have shared earlier as expected.

[MatthewC]

Hello Kanthu... well ok, just because it's a Friday and it's almost the weekend! I've written a specific query for you. It's quick & dirty and could probably be improved but if shoudl work.... it will give you the last issue updated (and when) for every project. you can set the date to show old projects. It doesn't have last updater because that information is in another table and it would require another join to do......

select 
proj.pkey as PROJECT_KEY, 
proj.pname AS PROJECT_NAME,
lastchange.pkey as ISSUE_KEY,
lastchange.reporter AS REPORTER,
lastchange.assignee AS ASSIGNEE,
lastchange.UPDATED as LAST_UPDATE,
lastchange.summary  AS ISSUE_SUMMARY
from project proj,
(
   select * from jiraissue ji , (
      select project as maxproject, max(updated) as lastupdate from jiraissue
       group by project
   ) lastji
   where ji.project=lastji.maxproject and ji.updated=lastji.lastupdate
) as lastchange

where proj.id=lastchange.project
and lastchange.updated <  '2012-08-07'
order by LAST_UPDATE

[srrekanth]

Hi Matthew,

we are performing in active projects through mysql DB so we required active and inactive projects plus last issue created in active and inactive projects.

[MatthewC]

Is it a piece of JQL you are after? Or it SQL? and you want a report containing the last created issue in every project, one issue per project?

What are you doing? Are you trying to find old projects?