JQL Relative Date Functions

[Stuart Steedman]

I'm just answering my own question here...

We run some of our reports for weeks that run Thursday-Wednesday.  Using standard JQL functions the basic functions sort of work, but when the week moves on on a Sunday, the reports are now a week ahead until the following Thursday.

None of the built-in JQL date functions allow any funny business.  Trust me, I've tried it.  So this is the solution I came up with.  Not exactly easy to maintain...

If you have a week that runs (inclusively) Monday-Sunday or Tuesday-Monday etc you will have to modify the following...

				Current Week			Coming Week			Last Week			Prior Week
		0	0	project = CCB and (	7	7	project = CCB and (	-7	-7	project = CCB and (	-14	-14	project = CCB and (
SUN	MON	1	1	(due = startofday(1d) and due = startOfWeek(1d)) or	8	8	(due = startofday(8d) and due = startOfWeek(8d)) or	-6	-6	(due = startofday(-6d) and due = startOfWeek(-6d)) or	-13	-13	(due = startofday(-13d) and due = startOfWeek(-13d)) or
	TUE	2	2	(due = startofday(2d) and due = startOfWeek(2d)) or	9	9	(due = startofday(9d) and due = startOfWeek(9d)) or	-5	-5	(due = startofday(-5d) and due = startOfWeek(-5d)) or	-12	-12	(due = startofday(-12d) and due = startOfWeek(-12d)) or
	WED	3	3	(due = startofday(3d) and due = startOfWeek(3d)) or	10	10	(due = startofday(10d) and due = startOfWeek(10d)) or	-4	-4	(due = startofday(-4d) and due = startOfWeek(-4d)) or	-11	-11	(due = startofday(-11d) and due = startOfWeek(-11d)) or
	THU	-3	-3	(due = startofday(-3d) and due = startOfWeek(-3d)) or	4	4	(due = startofday(4d) and due = startOfWeek(4d)) or	-10	-10	(due = startofday(-10d) and due = startOfWeek(-10d)) or	-17	-17	(due = startofday(-17d) and due = startOfWeek(-17d)) or
	FRI	-2	-2	(due = startofday(-2d) and due = startOfWeek(-2d)) or	5	5	(due = startofday(5d) and due = startOfWeek(5d)) or	-9	-9	(due = startofday(-9d) and due = startOfWeek(-9d)) or	-16	-16	(due = startofday(-16d) and due = startOfWeek(-16d)) or
	SAT	-1	-1	(due = startofday(-1d) and due = startOfWeek(-1d)) or	6	6	(due = startofday(6d) and due = startOfWeek(6d)) or	-8	-8	(due = startofday(-8d) and due = startOfWeek(-8d)) or	-15	-15	(due = startofday(-15d) and due = startOfWeek(-15d)) or
	SUN	0	0	(due = startofday(0d) and due = startOfWeek(0d)) or	7	7	(due = startofday(7d) and due = startOfWeek(7d)) or	-7	-7	(due = startofday(-7d) and due = startOfWeek(-7d)) or	-14	-14	(due = startofday(-14d) and due = startOfWeek(-14d)) or
MON	MON	0	1	(due = startofday(0d) and due = startOfWeek(1d)) or	7	8	(due = startofday(7d) and due = startOfWeek(8d)) or	-7	-6	(due = startofday(-7d) and due = startOfWeek(-6d)) or	-14	-13	(due = startofday(-14d) and due = startOfWeek(-13d)) or
	TUE	1	2	(due = startofday(1d) and due = startOfWeek(2d)) or	8	9	(due = startofday(8d) and due = startOfWeek(9d)) or	-6	-5	(due = startofday(-6d) and due = startOfWeek(-5d)) or	-13	-12	(due = startofday(-13d) and due = startOfWeek(-12d)) or
	WED	2	3	(due = startofday(2d) and due = startOfWeek(3d)) or	9	10	(due = startofday(9d) and due = startOfWeek(10d)) or	-5	-4	(due = startofday(-5d) and due = startOfWeek(-4d)) or	-12	-11	(due = startofday(-12d) and due = startOfWeek(-11d)) or
	THU	-4	-3	(due = startofday(-4d) and due = startOfWeek(-3d)) or	3	4	(due = startofday(3d) and due = startOfWeek(4d)) or	-11	-10	(due = startofday(-11d) and due = startOfWeek(-10d)) or	-18	-17	(due = startofday(-18d) and due = startOfWeek(-17d)) or
	FRI	-3	-2	(due = startofday(-3d) and due = startOfWeek(-2d)) or	4	5	(due = startofday(4d) and due = startOfWeek(5d)) or	-10	-9	(due = startofday(-10d) and due = startOfWeek(-9d)) or	-17	-16	(due = startofday(-17d) and due = startOfWeek(-16d)) or
	SAT	-2	-1	(due = startofday(-2d) and due = startOfWeek(-1d)) or	5	6	(due = startofday(5d) and due = startOfWeek(6d)) or	-9	-8	(due = startofday(-9d) and due = startOfWeek(-8d)) or	-16	-15	(due = startofday(-16d) and due = startOfWeek(-15d)) or
	SUN	-1	0	(due = startofday(-1d) and due = startOfWeek(0d)) or	6	7	(due = startofday(6d) and due = startOfWeek(7d)) or	-8	-7	(due = startofday(-8d) and due = startOfWeek(-7d)) or	-15	-14	(due = startofday(-15d) and due = startOfWeek(-14d)) or
TUE	MON	-1	1	(due = startofday(-1d) and due = startOfWeek(1d)) or	6	8	(due = startofday(6d) and due = startOfWeek(8d)) or	-8	-6	(due = startofday(-8d) and due = startOfWeek(-6d)) or	-15	-13	(due = startofday(-15d) and due = startOfWeek(-13d)) or
	TUE	0	2	(due = startofday(0d) and due = startOfWeek(2d)) or	7	9	(due = startofday(7d) and due = startOfWeek(9d)) or	-7	-5	(due = startofday(-7d) and due = startOfWeek(-5d)) or	-14	-12	(due = startofday(-14d) and due = startOfWeek(-12d)) or
	WED	1	3	(due = startofday(1d) and due = startOfWeek(3d)) or	8	10	(due = startofday(8d) and due = startOfWeek(10d)) or	-6	-4	(due = startofday(-6d) and due = startOfWeek(-4d)) or	-13	-11	(due = startofday(-13d) and due = startOfWeek(-11d)) or
	THU	-5	-3	(due = startofday(-5d) and due = startOfWeek(-3d)) or	2	4	(due = startofday(2d) and due = startOfWeek(4d)) or	-12	-10	(due = startofday(-12d) and due = startOfWeek(-10d)) or	-19	-17	(due = startofday(-19d) and due = startOfWeek(-17d)) or
	FRI	-4	-2	(due = startofday(-4d) and due = startOfWeek(-2d)) or	3	5	(due = startofday(3d) and due = startOfWeek(5d)) or	-11	-9	(due = startofday(-11d) and due = startOfWeek(-9d)) or	-18	-16	(due = startofday(-18d) and due = startOfWeek(-16d)) or
	SAT	-3	-1	(due = startofday(-3d) and due = startOfWeek(-1d)) or	4	6	(due = startofday(4d) and due = startOfWeek(6d)) or	-10	-8	(due = startofday(-10d) and due = startOfWeek(-8d)) or	-17	-15	(due = startofday(-17d) and due = startOfWeek(-15d)) or
	SUN	-2	0	(due = startofday(-2d) and due = startOfWeek(0d)) or	5	7	(due = startofday(5d) and due = startOfWeek(7d)) or	-9	-7	(due = startofday(-9d) and due = startOfWeek(-7d)) or	-16	-14	(due = startofday(-16d) and due = startOfWeek(-14d)) or
WED	MON	-2	1	(due = startofday(-2d) and due = startOfWeek(1d)) or	5	8	(due = startofday(5d) and due = startOfWeek(8d)) or	-9	-6	(due = startofday(-9d) and due = startOfWeek(-6d)) or	-16	-13	(due = startofday(-16d) and due = startOfWeek(-13d)) or
	TUE	-1	2	(due = startofday(-1d) and due = startOfWeek(2d)) or	6	9	(due = startofday(6d) and due = startOfWeek(9d)) or	-8	-5	(due = startofday(-8d) and due = startOfWeek(-5d)) or	-15	-12	(due = startofday(-15d) and due = startOfWeek(-12d)) or
	WED	0	3	(due = startofday(0d) and due = startOfWeek(3d)) or	7	10	(due = startofday(7d) and due = startOfWeek(10d)) or	-7	-4	(due = startofday(-7d) and due = startOfWeek(-4d)) or	-14	-11	(due = startofday(-14d) and due = startOfWeek(-11d)) or
	THU	-6	-3	(due = startofday(-6d) and due = startOfWeek(-3d)) or	1	4	(due = startofday(1d) and due = startOfWeek(4d)) or	-13	-10	(due = startofday(-13d) and due = startOfWeek(-10d)) or	-20	-17	(due = startofday(-20d) and due = startOfWeek(-17d)) or
	FRI	-5	-2	(due = startofday(-5d) and due = startOfWeek(-2d)) or	2	5	(due = startofday(2d) and due = startOfWeek(5d)) or	-12	-9	(due = startofday(-12d) and due = startOfWeek(-9d)) or	-19	-16	(due = startofday(-19d) and due = startOfWeek(-16d)) or
	SAT	-4	-1	(due = startofday(-4d) and due = startOfWeek(-1d)) or	3	6	(due = startofday(3d) and due = startOfWeek(6d)) or	-11	-8	(due = startofday(-11d) and due = startOfWeek(-8d)) or	-18	-15	(due = startofday(-18d) and due = startOfWeek(-15d)) or
	SUN	-3	0	(due = startofday(-3d) and due = startOfWeek(0d)) or	4	7	(due = startofday(4d) and due = startOfWeek(7d)) or	-10	-7	(due = startofday(-10d) and due = startOfWeek(-7d)) or	-17	-14	(due = startofday(-17d) and due = startOfWeek(-14d)) or
THU	MON	4	8	(due = startofday(4d) and due = startOfWeek(8d)) or	11	15	(due = startofday(11d) and due = startOfWeek(15d)) or	-3	1	(due = startofday(-3d) and due = startOfWeek(1d)) or	-10	-6	(due = startofday(-10d) and due = startOfWeek(-6d)) or
	TUE	5	9	(due = startofday(5d) and due = startOfWeek(9d)) or	12	16	(due = startofday(12d) and due = startOfWeek(16d)) or	-2	2	(due = startofday(-2d) and due = startOfWeek(2d)) or	-9	-5	(due = startofday(-9d) and due = startOfWeek(-5d)) or
	WED	6	10	(due = startofday(6d) and due = startOfWeek(10d)) or	13	17	(due = startofday(13d) and due = startOfWeek(17d)) or	-1	3	(due = startofday(-1d) and due = startOfWeek(3d)) or	-8	-4	(due = startofday(-8d) and due = startOfWeek(-4d)) or
	THU	0	4	(due = startofday(0d) and due = startOfWeek(4d)) or	7	11	(due = startofday(7d) and due = startOfWeek(11d)) or	-7	-3	(due = startofday(-7d) and due = startOfWeek(-3d)) or	-14	-10	(due = startofday(-14d) and due = startOfWeek(-10d)) or
	FRI	1	5	(due = startofday(1d) and due = startOfWeek(5d)) or	8	12	(due = startofday(8d) and due = startOfWeek(12d)) or	-6	-2	(due = startofday(-6d) and due = startOfWeek(-2d)) or	-13	-9	(due = startofday(-13d) and due = startOfWeek(-9d)) or
	SAT	2	6	(due = startofday(2d) and due = startOfWeek(6d)) or	9	13	(due = startofday(9d) and due = startOfWeek(13d)) or	-5	-1	(due = startofday(-5d) and due = startOfWeek(-1d)) or	-12	-8	(due = startofday(-12d) and due = startOfWeek(-8d)) or
	SUN	3	7	(due = startofday(3d) and due = startOfWeek(7d)) or	10	14	(due = startofday(10d) and due = startOfWeek(14d)) or	-4	0	(due = startofday(-4d) and due = startOfWeek(0d)) or	-11	-7	(due = startofday(-11d) and due = startOfWeek(-7d)) or
FRI	MON	3	8	(due = startofday(3d) and due = startOfWeek(8d)) or	10	15	(due = startofday(10d) and due = startOfWeek(15d)) or	-4	1	(due = startofday(-4d) and due = startOfWeek(1d)) or	-11	-6	(due = startofday(-11d) and due = startOfWeek(-6d)) or
	TUE	4	9	(due = startofday(4d) and due = startOfWeek(9d)) or	11	16	(due = startofday(11d) and due = startOfWeek(16d)) or	-3	2	(due = startofday(-3d) and due = startOfWeek(2d)) or	-10	-5	(due = startofday(-10d) and due = startOfWeek(-5d)) or
	WED	5	10	(due = startofday(5d) and due = startOfWeek(10d)) or	12	17	(due = startofday(12d) and due = startOfWeek(17d)) or	-2	3	(due = startofday(-2d) and due = startOfWeek(3d)) or	-9	-4	(due = startofday(-9d) and due = startOfWeek(-4d)) or
	THU	-1	4	(due = startofday(-1d) and due = startOfWeek(4d)) or	6	11	(due = startofday(6d) and due = startOfWeek(11d)) or	-8	-3	(due = startofday(-8d) and due = startOfWeek(-3d)) or	-15	-10	(due = startofday(-15d) and due = startOfWeek(-10d)) or
	FRI	0	5	(due = startofday(0d) and due = startOfWeek(5d)) or	7	12	(due = startofday(7d) and due = startOfWeek(12d)) or	-7	-2	(due = startofday(-7d) and due = startOfWeek(-2d)) or	-14	-9	(due = startofday(-14d) and due = startOfWeek(-9d)) or
	SAT	1	6	(due = startofday(1d) and due = startOfWeek(6d)) or	8	13	(due = startofday(8d) and due = startOfWeek(13d)) or	-6	-1	(due = startofday(-6d) and due = startOfWeek(-1d)) or	-13	-8	(due = startofday(-13d) and due = startOfWeek(-8d)) or
	SUN	2	7	(due = startofday(2d) and due = startOfWeek(7d)) or	9	14	(due = startofday(9d) and due = startOfWeek(14d)) or	-5	0	(due = startofday(-5d) and due = startOfWeek(0d)) or	-12	-7	(due = startofday(-12d) and due = startOfWeek(-7d)) or
SAT	MON	2	8	(due = startofday(2d) and due = startOfWeek(8d)) or	9	15	(due = startofday(9d) and due = startOfWeek(15d)) or	-5	1	(due = startofday(-5d) and due = startOfWeek(1d)) or	-12	-6	(due = startofday(-12d) and due = startOfWeek(-6d)) or
	TUE	3	9	(due = startofday(3d) and due = startOfWeek(9d)) or	10	16	(due = startofday(10d) and due = startOfWeek(16d)) or	-4	2	(due = startofday(-4d) and due = startOfWeek(2d)) or	-11	-5	(due = startofday(-11d) and due = startOfWeek(-5d)) or
	WED	4	10	(due = startofday(4d) and due = startOfWeek(10d)) or	11	17	(due = startofday(11d) and due = startOfWeek(17d)) or	-3	3	(due = startofday(-3d) and due = startOfWeek(3d)) or	-10	-4	(due = startofday(-10d) and due = startOfWeek(-4d)) or
	THU	-2	4	(due = startofday(-2d) and due = startOfWeek(4d)) or	5	11	(due = startofday(5d) and due = startOfWeek(11d)) or	-9	-3	(due = startofday(-9d) and due = startOfWeek(-3d)) or	-16	-10	(due = startofday(-16d) and due = startOfWeek(-10d)) or
	FRI	-1	5	(due = startofday(-1d) and due = startOfWeek(5d)) or	6	12	(due = startofday(6d) and due = startOfWeek(12d)) or	-8	-2	(due = startofday(-8d) and due = startOfWeek(-2d)) or	-15	-9	(due = startofday(-15d) and due = startOfWeek(-9d)) or
	SAT	0	6	(due = startofday(0d) and due = startOfWeek(6d)) or	7	13	(due = startofday(7d) and due = startOfWeek(13d)) or	-7	-1	(due = startofday(-7d) and due = startOfWeek(-1d)) or	-14	-8	(due = startofday(-14d) and due = startOfWeek(-8d)) or
	SUN	1	7	(due = startofday(1d) and due = startOfWeek(7d)) or	8	14	(due = startofday(8d) and due = startOfWeek(14d)) or	-6	0	(due = startofday(-6d) and due = startOfWeek(0d)) or	-13	-7	(due = startofday(-13d) and due = startOfWeek(-7d)) or
				)			)			)			)
				ORDER BY due ASC, key ASC			ORDER BY due ASC, key ASC			ORDER BY due ASC, key ASC			ORDER BY due ASC, key ASC

[Stuart Steedman]

Oh, by the way you need to remove the "or" from the last row - these were generated in Excel and I forgot to update the last row...