MySql SELECT WHERE Date is greater than past 10 weeks (starting at prev Sunday)

Question

The problem I'm running into is when running this query it starts the week interval from the current date (minus 7 days from current date). I want to do all previous 10 weeks (Sun-Mon). So if my current date is on a Monday, Tuesday, or Wednesday, I want it to start on the most recent Sunday then look back at the previous 10 weeks. I believe I have to use WEEKDAY or DAYOFWEEK in the DATE_SUB but have tried with no avail:

SELECT
    SUM(MT.QUANTITY) as qty,
    WEEKDAY(MT.TRANS_DATE) AS WEEKDAY,
    WEEK(MT.TRANS_DATE) AS WEEK,
    YEAR(MT.TRANS_DATE) AS YEAR
FROM
    mytable MT
WHERE
        MT.TRANS_DATE > DATE_SUB(CURDATE(), INTERVAL 10 WEEK)
GROUP BY WEEK

Answer 1

Try with DATE_SUB(STR_TO_DATE(CONCAT(YEAR(CURDATE()),WEEK(CURDATE()),' Sunday'), '%X%V %W'),INTERVAL 10 WEEK), like this:

SELECT
    SUM(MT.QUANTITY) as qty,
    WEEKDAY(MT.TRANS_DATE) AS WEEKDAY,
    WEEK(MT.TRANS_DATE) AS WEEK,
    YEAR(MT.TRANS_DATE) AS YEAR
FROM
    mytable MT
WHERE
        MT.TRANS_DATE > DATE_SUB(STR_TO_DATE(CONCAT(YEAR(CURDATE()),WEEK(CURDATE()),' Sunday'), '%X%V %W'),INTERVAL 10 WEEK)
GROUP BY WEEK

Also you can use the other way pilcrow suggested:

SUBDATE(CURDATE(), DAYOFWEEK(CURDATE()) - 1)

Which is a little more readable...