SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast sffc's Avatar
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Select Next Available Week

    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.
    "I haven't failed, I just found
    100,000 ways that don't work"
    Thomas Edison

  2. #2
    SitePoint Enthusiast sffc's Avatar
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I got the query. For what it's worth:
    Code MySQL:
    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!
    "I haven't failed, I just found
    100,000 ways that don't work"
    Thomas Edison

  3. #3
    SitePoint Enthusiast sffc's Avatar
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

    Code MySQL:
    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
    "I haven't failed, I just found
    100,000 ways that don't work"
    Thomas Edison


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •