CODEWITHSUNDEEP
mysql
Z Holt
Z Holt

Reputation: 141

MySQL date between last monday and the next sunday

i'm looking to find all records that have a booking date between the previous monday and the next sunday in MySQL.

So far I have:

SELECT firstname
     , lastname
     , sessions
     , (SELECT COUNT(memberid) 
          FROM bookings
         WHERE m.memberid = b.memberid  
            and b.date between lastMonday and nextSunday) as sessionsused 
  from members

I'm looking what to substitute into the lastmonday and nextsunday

Any help is much appreciated!

Upvotes: 0

Views: 1089

Answers (1)

Matt Raines
Matt Raines

Reputation: 4218

MySQL's YEARWEEK() function selects a unique value for each week that you can use for comparison. It takes a second parameter which specifies whether weeks start on Sunday (0) or Monday (1).

SELECT COUNT(memberid) 
FROM bookings
WHERE m.memberid = b.memberid  
AND YEARWEEK(b.date, 1) = YEARWEEK(NOW(), 1);

This will always select rows where b.date is in the current week. For a specific week in the past, change NOW() for whatever date expression you require.

For the more generic case where your week does not start on a Sunday or a Monday, you will need some slightly more complicated logic. Here you substitute @weekday with the day on which your weeks begin, 2 = Tues, 3 = Wed, 4 = Thu, 5 = Fri, 6 = Sat.

SELECT COUNT(memberid) 
FROM bookings
WHERE m.memberid = b.memberid  
AND DATE(b.date)
    BETWEEN DATE_SUB(DATE(NOW()), INTERVAL (WEEKDAY(NOW()) - @weekday + 7) % 7 DAY)
        AND DATE_ADD(DATE(NOW()), INTERVAL 6 - (WEEKDAY(NOW()) - @weekday + 7) % 7 DAY);

Upvotes: 2

Related Questions