Advanced search using jql for 3 possibly different date perimeters.

[Eric Sebian]

I have 3 dates that I want to query and return results on. I have a Start Date, End Date, and Transport date. The end dates are usually the same but there is the rare time that the end date is later than the transport date (because the change can't be verified until the following day). I need to be able to return results of a search from the current Tuesday to the following Tuesday, of all 3 dates perimeters. Is that possible with just jql? I also have the scriptrunner add-on if that helps. Please advise.

[Joshua Sneed Contegix]

Hi Eric,

The JQL format is going to be something like this:

project = "Project Name" and (dateStatement #1 OR dateStatement #2 OR dateStatement #3)

 I recommend you review Atlassian's JQL documentation and mind the links on the right side of those pages. Here are some to get you started.

• https://support.atlassian.com/jira-service-management-cloud/docs/use-advanced-search-with-jira-query-language-jql/
• https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-939938733.html

Cheers!

[Trudy Claspill]

I am unclear what criteria you are trying to implement in your filter. Can you please clarify that?

Are you trying to set a criteria to compare one of these date fields to one of the others?

Are you trying to get results for issues where one of these dates falls within a date range?

[Eric Sebian]

I'm trying to list out the all requests that have end dates between this Tuesday and the next. For example, today is the 15th. I have 5 requests.

ABC-1 start date = 6/18,  end date = 6/22, transport date = 6/19

ABC-2 start date = 6/16,  end date = 6/20, transport date = 6/20

ABC-3 start date = 6/15,  end date = 6/21, transport date = 6/19

ABC-4 start date = 6/23,  end date = 6/24, transport date = 6/24

ABC-5 start date = 6/12,  end date = 6/19, transport date = 6/19

the jql should list:

ABC-1

ABC-2

ABC-3

ABC-5

[Eric Sebian]

Does this makes sense? Because it's not working to find those requests between the Tuesdays. 

project = cms and ("End Date/Time" >= startOfWeek(2d)) or ("Transport Live Date" >= startOfWeek(2d)) and ("End Date/Time" <= endOfWeek(3d)) or ("Transport Live Date" >= endOfWeek(3d))

[Joshua Sneed Contegix]

Hi Eric,

It appears as though your logic may be broken in the query, ironically, due to the lack of parenthesis. You may also need to review the operators, date and date ranges are tricky in JQL. Try something like below and swap >= for <= if not pulling the results you expect.

project = cms and (("End Date/Time" >= startOfWeek(2d)) OR ("Transport Live Date" >= startOfWeek(2d))) and (("End Date/Time" <= endOfWeek(3d)) OR ("Transport Live Date" >= endOfWeek(3d)))

 Cheers!