SQL query to list all projects in category X with recent updated date

[jmohr2]

Hi,

I'm looking for a query to list all projects associated with a particular category and with the recent update date.

Thanks :)

[Nic Brough -Adaptavist-]

Projects don't have an update date, so can you tell us what you're planning to use instead?

[Jack Brickey]

LOL.... i totally missed the "project" here and was simply assuming "issues".

[Jack Brickey]

not sure what you mean by particular category which is important to understand to be able to provide a jql but...

<some particular category jql here> AND updated > -5d

replace the 5 w/ whatever you consider recent

of if you are asking for a "specific date"

<some particular category jql here> AND updated = updated > '2019/01/24'

change the date as required

[Tarun Sapra]

Hello @jmohr2

I think you mean , you are looking for JQL.

Here's what will work for you

category = "<your category name here>" and updatedDate > startOfMonth() 

Projects in a specific category and updated in this month. 

[Tarun Sapra]

Querying JIRA DB is not straightforward and not recommended when something is already provided via JQL.

[Alexey Matveev]

Hello,

Join three tables: jiraissue, project and projectcategory

[Никитоссс Картавый]

не совсем понятно как их объединить, если у projectcategory нет общих столбцов с остальными таблицами 

[Nic Brough -Adaptavist-]

It does - look at the IDs in the projectcategory table, you'll see them in the project table.

This sort of complexity is one of the reasons we all tell you not to bother looking at the database.  It's the worst possible way to report on a Jira system.