JQL Query: Is there a way to query the following in JQL?

Question

Per Epic
Total number of issues (completed/not completed)--- no. of hours used to complete per Issue (per Epic total) – Issues/hours that were underestimated/overestimated -- planned/unplanned Issues and hours breakdown (%) etc. –

I tried the following and couldn't make it work as there is a limitation in using variables in JQL. Please let me know.

project = <MY_PROJECT> AND status in (Open, "In Progress",Resolved, Done) AND remainingEstimate != originalEstimate ORDER BY "Epic Name"

ERROR : The value 'originalEstimate' for field 'remainingEstimate' is invalid. Please specify a positive duration format; for example: '1h 30m', '2d'.

Based on the error, I see the remainingEstimate and originalEstimate must be mentioned as variables inorder for the get the values for the respective variables. I couldn't get lucky using variables using $ or another expression.

Note : Above query is incomplete, as the syntax error didn't let me to write filters for remaining metrics to proceed further

Contact Kintosoft · Answer

SQL for JIRA also supports comparisons based on regular and custom fields among any other JIRA data. Example: select i.key from issues i where JQL=' project = AND status in (Open, "In Progress",Resolved, Done) ORDER BY "Epic Name" ' and i.originalestimate = i.remainingestimate Then convert back the SQL above into a JIRA JQL: issue in sql("")

Petar Petrov _Appfire_ · Answer

You can't compare fields in JQL like that - if you have Script Runner use the expression function.