I have a table with the fields ItemID [smallint], wk [tinyint(2) unsigned zerofill], and yr [smallint(4) unsigned zerofill]. Suppose the table contained the following data:
Code:
+------+----+----+
|ItemID|wk  |yr  |
+------+----+----+
|1     |51  |2010|
|2     |51  |2010|
|3     |52  |2010|
|4     |52  |2010|
|5     |01  |2011|
|6     |01  |2011|
|7     |03  |2011|
+------+----+----+
Each week in each year can have a maximum of 2 rows. What I need is a query that finds the next week in the future that has fewer than 2 rows assigned to it. I came up with the following query:
Code MySQL:
SELECT CONCAT(yr, "-W", wk) AS nextavail
FROM (
  SELECT COUNT(*) AS cnt, wk, yr
  FROM MyTable
  GROUP BY yr, wk
  ORDER BY yr ASC , wk ASC
) t1
WHERE CONCAT(yr, wk) >= YEARWEEK(NOW(), 6)
AND cnt < 2
LIMIT 1
This query works, but it only selects weeks for which there already exists a row in the table. With the data I listed above, this query would return "2011-W03" instead of what I need, which would be "2011-W02".

What query would achieve what I want to do?

Note: Since only administrators would be using this query, I'm not too worried about its efficiency.