Filter to return results from Due Date or Extended Date

[Kate]

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.

[Piyush Singh · Accepted solution]

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]

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!!

[Omprakash Thamsetty]

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