Select Next Available Week

[sffc]

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:

+------+----+----+
|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:

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.

[sffc]

Okay, I got the query. For what it's worth:

SELECT * 
FROM (
   SELECT COUNT( * ) AS cnt, i1.incwkyr
   FROM (
      SELECT YEARWEEK( DATE_ADD( STR_TO_DATE( CONCAT( yr, wk,  ' Sunday' ) ,  '%X%V %W' ) , INTERVAL 1 WEEK ) , 6 ) AS incwkyr
      FROM YearWeekTable
      UNION DISTINCT (
         SELECT YEARWEEK( NOW( ) )
      )
   )i1
   LEFT JOIN YearWeekTable i2
    ON i1.incwkyr = CONCAT( i2.yr, i2.wk ) 
   WHERE incwkyr >= YEARWEEK( NOW( ) ) 
   GROUP BY incwkyr
   ORDER BY incwkyr ASC
)t1
WHERE t1.cnt <2
LIMIT 1

This query returns "201102" for the example data set above.

I got some of my inspiration from here. Basically, using a subquery, I first make table "i1". This table contains all current values of our table increased by 1 week. I added the "union" clause to ensure that the current week is part of the result. Then, I left join this table back to the original table to see how many fields exist with the "increased" week and year. It is essential that it is a left join, because if it weren't, weeks with no rows would not exist in the result set. (With the left join, we get "null" values.) Then, I test to find the next week in the future that has fewer than 2 rows.

To make my query look for weeks with 3 or more rows, I would put the new number in the line "WHERE t1.cnt < #". On the other hand, if I wanted the next week for which no weeks existed, I would modify the query to test for NULL values after the left join.

I hope this post helps someone in the future!

[sffc]

As a follow-up, here is a version of this query that will work to find the next week that has no value assigned to it (maximum per week = 1).

select incwkyr from (
	select YEARWEEK( DATE_ADD( STR_TO_DATE( concat( yr, wk, ' Sunday' ), '%X%V %W' ), INTERVAL 1 WEEK ), 6 ) as incwkyr
	from YearWeekTable
	union distinct (
		select YEARWEEK( NOW() )
	)
) i1
left join YearWeekTable i2
  ON i1.incwkyr=concat( i2.yr, i2.wk )
where incwkyr>=YEARWEEK( NOW() )
  and i2.yr is null
order by incwkyr asc limit 1