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