Filter to return results from Due Date or Extended Date

Kate July 12, 2023

Help please?

I have a team that completes work that has some pretty strict SLA's and auditability requirements on their work. They needed that the 'Calc Due Date' would generate based on the date the ticket was lodged. Once lodged, this date cannot change as it has to reported on. By exception, there will be situations where agreed upon delays to that due date can occur.

I have automations auto-filling the Due Date (which is then not-editable); easy.
If a user has to extend the Due Date out, there is a field "Extended Due Date" that is populated as part of the process.

What I am now trying to do is generate a JQL query that will return a list of work that is due in the next 2 days; this would be based on the 'Calc. Due Date' UNLESS there is the 'Extended Due Date' is populated, when it should return results based on that date.

1 answer

1 accepted

4 votes
Answer accepted
Piyush Singh July 13, 2023

To generate a JQL query that returns a list of work due in the next 2 days, considering both the 'Calc Due Date' and 'Extended Due Date' fields, you can use the following query:

(project = YourProject AND
((DueDate >= startOfDay() AND DueDate <= endOfDay("+2d")) OR
(ExtendedDueDate >= startOfDay() AND ExtendedDueDate <= endOfDay("+2d"))))

Explanation of the query:

  • Replace "YourProject" with the name of your project or the appropriate project identifier.
  • The query checks two conditions using the logical OR operator:
    - If the 'DueDate' field falls within the next 2 days.
    - If the 'ExtendedDueDate' field falls within the next 2 days.
  • The startOfDay() and endOfDay() functions are used to ensure the query includes work due for the entire day.

    By using this JQL query, you will get a list of work items that are due in the next 2 days based on the 'Calc Due Date' unless the 'Extended Due Date' field is populated, in which case it will consider the 'Extended Due Date' for those specific items.
Kate July 16, 2023

Thank you so much @Piyush Singh

Now I can see exactly where I went wrong with my own attempts at this went awry. This JQL works perfectly. Thank you, thank you, thank you!!

Like Piyush Singh likes this
Omprakash Thamsetty
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 18, 2024

@Piyush Singh How to get past or over due issues with the same due date and extension date?

Suggest an answer

Log in or Sign up to answer
Site Admin
AUG Leaders

Atlassian Community Events