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
SELECT COUNT(*) AS cnt, wk, yr
GROUP BY yr, wk
ORDER BY yr ASC , wk ASC
WHERE CONCAT(yr, wk) >= YEARWEEK(NOW(), 6)
AND cnt < 2
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.
Okay, I got the query. For what it's worth:
SELECT COUNT( * ) AS cnt, i1.incwkyr
SELECT YEARWEEK( DATE_ADD( STR_TO_DATE( CONCAT( yr, wk, ' Sunday' ) , '%X%V %W' ) , INTERVAL 1 WEEK ) , 6 ) AS incwkyr
UNION DISTINCT (
SELECT YEARWEEK( NOW( ) )
LEFT JOIN YearWeekTable i2
ON i1.incwkyr = CONCAT( i2.yr, i2.wk )
WHERE incwkyr >= YEARWEEK( NOW( ) )
GROUP BY incwkyr
ORDER BY incwkyr ASC
WHERE t1.cnt <2
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!
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
union distinct (
select YEARWEEK( NOW() )
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