dateCompare jql scriptrunner function

Question

Hi Team,

I am trying to compare two date fields and see if they are more significant than "X" value. I am trying with these two JQLs, but not getting any results, but we have tickets. Are these jqls queries correct or do we need to make any changes ?

project = "TEST" AND (filter = "Team 3" or filter = "Team 4") and status in (Backlog, "In Progress") AND issueFunction in dateCompare(" ", "Prod Accept Stamped Date > Implementation Stamped Date +30d")

project = BIIMPL AND  (filter in ("BIIMPL Team 3") OR filter in ("BIIMPL Team 4")) AND status in (Backlog, "In Progress")  and issueFunction in dateCompare("", "Prod Accept Stamped Date - Implementation Stamped Date > 30" )

Ram Kumar Aravindakshan _Adaptavist_ · Answer

Hi @jira_admin_cu

For your requirement, you could try something like this:-

import com.adaptavist.hapi.jira.issues.Issues
import groovy.xml.MarkupBuilder

def issues = Issues.search('project = MOCK') // Change the Project Key

def emailBody = new StringWriter()
def html = new MarkupBuilder(emailBody)

html.html {
    head {
        style(type:'text/css', """
            table { border-collapse: collapse; width: 100%; }
            th, td { text-align: left; padding: 8px; }
            tr:nth-child(even){background-color: #f2f2f2}
            th { background-color: #04AA6D; color: white; }""".toString() )
    }
    body  {
        table  {
            thead  {
                tr  {
                    th 'Issue Key'
                    th 'Sample Date'
                    th 'Baseline Date'
                }
                issues.each {
                    def issueKey = it.key
                    def sampleDate = it.getCustomFieldValue('Sample Date') //Change the field name
                    def baselineDate = it.getCustomFieldValue('Baseline Date') //Change the field name
                    tr {
                        td ( issueKey )
                        td ( sampleDate )
                        td ( baselineDate )
                    }
                }
            }
        }
    }
}

emailBody.toString()

Please note that the sample working code above is not 100% exact to your environment. Hence, you must make the required modifications.

Below is the sample output returned:-

Also in your description, you mentioned:-

I am trying to compare two date fields and see if they are more significant than "X" value

How much date range comparison is expected between the two date fields? And also when comparing the date range, do you have any other numeric field to set the difference?

I am looking forward to your feedback.

Thank you and Kind regards,
Ram

jira_admin_cu · Answer

Hi @Ram Kumar Aravindakshan _Adaptavist_

We can try with REST endpoint also.

Ram Kumar Aravindakshan _Adaptavist_ · Answer

Hi @jira_admin_cu

To clarify, is JQL the only way you want to use to compare the value of the date fields, or would a REST Endpoint suffice, i.e. to pull the values of the fields from all the issues and display the result in a table or csv?

Please clarify so I can provide a sample.

Than you and Kind regards,
Ram