I haven't gotten to spend very much time on this at all today, so I'm not sure if I've given up on the brevity game (although I'm quickly losing confidence that there is a shorter solution I'm capable of finding), but I figured I'd at least talk about the issues with the original version of the query.
First, the logic underlying the query actually handles negative offsets from the anchor date just fine. The trick is that the logic is based on the floor of division by 7, which conveniently matches integer division by 7, as long as the dividend is also positive. Once the dividend is negative, integer division by 7 gives the ceiling instead of the floor, and the solution breaks down.
There are a number of ways around this.
The brute method, and not surprisingly the slowest under every condition I tested (by a factor of more than 2 compared to the original), is to wrap each division in FLOOR and change the divisor to 7.0.
You could also implement floor using only integer math, which as far as I know is most easily done by finding the ceiling of the division (add divisor-1 to the dividend) and subtracting 1 from that result.
This works, and across the conditions I tested, increases CPU time by ~25% compared to the original. It also has the downside of adding quite a few characters to the solution and making the expression even less readable than it already is.
Since integer division already gives the floor of the division for positive divisors and dividends, another idea would be to adjust only the negative numbers, if there are any, so that integer division by 7 gives the floor of division of the original number by 7. I implemented this using a CASE expression to adjust the anchor date appropriately if either start or end date were before the original anchor date.
This at least leaves the original expression intact, but adds a lot of characters, and generally performed very slightly worse than the hand-rolled integer math implementation of floor on all divisions, whether they needed it or not.
Finally, in what I can't decide is either the most elegant or crudest of the solutions, since only the relative positions to the anchor matter, not the absolute size of the offsets, you can just add some sufficiently large multiple of 7 to the DATEDIFF(DAY... expressions in the VALUES constructor to make sure that even if a date is 0001-01-01, the resulting offset value can't be negative.
This added the fewest characters, and generally performed better than the others (typically around ~10-15% increase in CPU compared to the original), although the integer implementation of floor sometimes edged it out, but probably only because of random CPU timing quirks on my laptop.
The second problem is the "can't always push the anchor date further back" problem, which affects the knee-jerk reaction to problem 1 of just moving the anchor date back.
All of the above solutions ignore this completely, since they work regardless of what the anchor date is (as long as it's a Sunday).
There is one other solution logically similar to the original that performs reasonably well (usually similar to the integer floor implementations, but sometimes almost as fast as the original), is pretty concise (but not as concise as either the original or the original with a large positive addition to the offset), arguably easier to understand than the original, but technically susceptible to the "can't always go further back" problem.
This approach is to change the VALUES constructor to compute the day before start_date and the day after end_date. It then just adds DATEDIFF(WEEK,start_date,end_date) and DATEDIFF(WEEK,day_before_start, day_after_end). This works because DATEDIFF(WEEK.... is NOT datefirst dependent, and always uses Sunday.
Obviously if your start date is the very first allowed date or if your end date is the very last allowed date, this won't work, so it doesn't completely solve all the problems. Its decent performance, brevity, and readability probably make it one of the better choices if you don't mind the edge case it has issues with.
All in all, though, it looks like the simplest way to get around the listed problems and maintain most of the performance of the original (and keep the query short, since I'm all about imposing that arbitrary constraint on myself when I do things in my spare time for fun 🙂 ) is to add a sufficiently large multiple of 7 to the DATEDIFFs to eliminate the possibility of a negative offset.
EDIT: I posted this before catching up on the thread (apparently I'm adopting more and more bad habits in my increasing age, which doesn't bode well).
Thanks for all that testing Jeff!
Acknowledging the edge cases and not using the original solution on data for which it is not suited is likely even better than all the fiddling I talked about above to get around some of the known edge cases, but I think my train mostly left the "Is this practical?" station on this problem a while back 🙂 (although I haven't done any rigorous performance testing on other solutions, so it's possible that there's some practical performance benefit to such a solution).