...
This query treats issues foung from JQL 'project = SSP' as a table with columns 'issuetype,status,issuekey,created,updated'. Example results can be following:
| issuetype | status | EXPR$2 | EXPR$3 | EXPR$4 |
|---|---|---|---|---|
| 10001 | 10001 | 8 | 2018-06-12 13:38:35 | 2018-07-08 00:18:35 |
| 10001 | 10000 | 8 | 2018-07-09 14:42:35 | 2018-07-10 18:52:38 |
| 10101 | 3 | 2 | 2018-07-06 21:44:35 | 2018-07-07 19:09:35 |
| 10001 | 3 | 1 | 2018-07-03 09:42:35 | 2018-07-03 09:42:35 |
| 10102 | 10001 | 1 | 2018-07-03 09:42:35 | 2018-07-07 02:18:35 |
| 10101 | 10000 | 1 | 2018-07-10 18:52:37 | 2018-07-10 18:52:37 |
| 10102 | 10000 | 2 | 2018-07-03 09:42:35 | 2018-07-10 18:52:38 |
| Tip |
|---|
For converting your Jira searches and any JQL you can start with Jira builtin navigator and then export prepared JQL query to SmartQL, see Exporting issue searches & JQL queries to SmartQL |
...
Example results can be following:
| Issue type | Status | EXPR$2 | EXPR$3 | EXPR$4 |
|---|---|---|---|---|
| Sub-task | To Do | 1 | 2018-07-10 18:52:37 | 2018-07-10 18:52:37 |
| Story | Done | 8 | 2018-06-12 13:38:35 | 2018-07-08 00:18:35 |
| Story | To Do | 8 | 2018-07-09 14:42:35 | 2018-07-10 18:52:38 |
| Sub-task | In Progress | 2 | 2018-07-06 21:44:35 | 2018-07-07 19:09:35 |
| Bug | Done | 1 | 2018-07-03 09:42:35 | 2018-07-07 02:18:35 |
| Bug | To Do | 2 | 2018-07-03 09:42:35 | 2018-07-10 18:52:38 |
| Story | In Progress | 1 | 2018-07-03 09:42:35 | 2018-07-03 09:42:35 |
Lateral joining JQL with SQL
JQL queries can also be computed for each row of other table or view, by using lateral joins. Following query shows first 5 agile board to issue mappings.
| Code Block | ||
|---|---|---|
| ||
SELECT b.NAME, j.key
FROM SOFTWARE.BOARDS b
JOIN CORE.FILTERS f on f.ID = b.SAVEDFILTER
CROSS JOIN LATERAL TABLE(AUX.JQL('key', f.query)) j
LIMIT 5 |
Example result:
| NAME | key |
|---|---|
| SSP board | SSP-1 |
| SSP board | SSP-2 |
| SSP board | SSP-3 |
| SSP board | SSP-4 |
| SSP board | SSP-5 |