We have a number of recurring maintenance windows every month which are specified by the week number and the day of the week (ex. second Wednesday every month). I need to be able to write a query to show me all the tickets due the second wednesday of every month and the second sunday of every month. I can sort of specify the second week of the month using startOfMonth +/- and filtering for the 8th to the 14th, but I can't see a way to get it to only show me tickets that are due on a Wednesday. It seems that startOfWeek and endOfWeek only works to specify a day of the week for the past 7 days.
Is there not a way to get a list of all tickets that are due this month on a Wednesday?
startOfWeek(3d), startOfWeek(10d), startOfWeek(17d), startOfWeek(-4d) are all Wednesdays, so something like:
duedate = startOfWeek(-25d) OR duedate = startOfWeek(-18d) OR duedate = startOfWeek(-11d) OR duedate = startOfWeek(-4d) OR duedate = startOfWeek(3d) OR duedate = startOfWeek(10d) OR duedate = startOfWeek(17d) OR duedate = startOfWeek(24d)
should identify all Wednesdays from today back to the beginning of the month as well as forward to the end of the month (plus a bit into the previous month or forward to the next month, depending upon which week you're in). If you then combine that with your idea of limiting to the 8th through the 14th utilizing startOfMonth(), something like the following may be what you need:
duedate >= startOfMonth("+7d") AND duedate <= startOfMonth("+13d") AND (duedate = startOfWeek(-25d) OR duedate = startOfWeek(-18d) OR duedate = startOfWeek(-11d) OR duedate = startOfWeek(-4d) OR duedate = startOfWeek(3d) OR duedate = startOfWeek(10d) OR duedate = startOfWeek(17d) OR duedate = startOfWeek(24d) )
No, as soon as you hit the 1st day of a new month, the filters should then list issues for the new month, thanks to the duedate >= startOfMonth("+7d") AND duedate <= startOfMonth("+13d") portion of the JQL. The numbers in the startOfWeek() calls (-4, 3, 10, etc.) will always work, as those are offsets in relation to Sunday (day 0).
You both almost have it!
Now that you've got a method for getting issues due on Wednesdays, as well as a range of days of month where the a second weekday can occur, you just need to combine both queries in an appropriate manner:
And you got it! :)
@Martha Sapeta - Glad you're getting close! Due Date is a date field (no time component), so checking for equality (e.g. duedate = startOfWeek(3d)) works. For a datetime field, though, it needs to be expanded a bit like this: maintenance >= startOfWeek(3d) and maintenance < startofWeek(4d). So, you'll want to replace each startOfWeek() check with a pair like that. i.e.
duedate >= startOfMonth("+7d") AND duedate <= startOfMonth("+13d") AND ( (maintenance >= startOfWeek(-25d) and maintenance < startOfWeek(-24d) ) OR (maintenance >= startOfWeek(-18d) and maintenance < startOfWeek(-17d) ) OR ...
Someone with coding skills might find inspiration by having a look at these sources:
Maybe a custom field set by a Script Listener listening for Issue Edited events can do the job:
Or maybe a Scripted Field:
Thanks for the response! We've basically done the setup you suggested: we already had an issue type for Maintenance, and we've also added a Maintenance Close Window that is a specific date, so I can search for those (we still have Due Date as well). The problem is that it will take some time to update all the tickets, as well as getting people to put in the maintenance tickets correctly in the future, so I was hoping for some sort of work around in the meantime. Additionally, this setup allows me to display all the maintenance tickets for a given time frame, but I still can't display only the tickets for the next maintenance window, unless I hard-code the date (which then has to be updated every month).
However, I'm told we do have ScriptRunner, though I've never used it before. Would I be able to create a script to search for things like tickets due every second Wednesday of the month?
I make script field in adaptavist scriptrunner:
if (!issue.getResolutionDate()) return -1
def timestamp = issue.getResolutionDate()
def cal = Calendar.getInstance()
double weekday = cal.get(Calendar.DAY_OF_WEEK) - 1;
if (!weekday) return 7
1 - Monday
2 - Tuesday
7 - Sunday
-1 - issue not resolved
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events