nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

All comments

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?

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

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 &gt; '2011-07-01 00:00:00' AND updated &gt; '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 &amp; 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 &lt; 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

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;