Calendar: x weeks ago always starting on a Sunday

[sophia001]

The result of the query should be all dates from today - 5 weeks ago, where the first day is a Sunday using a calendar table specifically for this business:

The current week is defined as w/e 15th Jan and it's the 5 weeks back from then so start w/e 11th Dec

e.g.

select distinct day_date, day_name, week_number, weekstart_date
from
calendar
where
day_date between (curdate() - INTERVAL 35 day) and curdate()

returns data from

09/12/2011 - today, which is fine, but the first date I am interested in is the Sunday:

day_date day_name week_number weekstart_date weekend_date
2011-12-09 00:00:00 Friday 50 2011-12-05 00:00:00 2011-12-11 00:00:00
2011-12-10 00:00:00 Saturday 50 2011-12-05 00:00:00 2011-12-11 00:00:00
2011-12-11 00:00:00 Sunday 51 2011-12-05 00:00:00 2011-12-11 00:00:00

.....


2012-01-12 00:00:00 Thursday 2 2012-01-09 00:00:00 2012-01-15 00:00:00
2012-01-13 00:00:00 Friday 2 2012-01-09 00:00:00 2012-01-15 00:00:00


What SQL do I write to ensure that the first Sunday will always be selected as the first day of the date range?

[Rabbit]

Well you need to account for the current date's weekday.

You need to take 35, add 7, (or just take 42) and then subtract the weekday() of the current date. With the exception of sunday of course.