hometeam - varchar awayteam - varchar weeknumber - int
Now the catch is that we want to know what weeks each team DOESN’T have a record…usually when you are dealing with queries you are looking FOR records, not the absence of them…so trying to tackle this in one query (like I love to do) was making my brain do flips! In fact, when I first wrote the code for this page for Draftwizard, I gave up and took the easy path…I looped through the week numbers, and then just did a query to see what teams didn’t have that week…it worked fine. However, it meant I was doing 17 queries every time someone hit this page! These are the specific kind of ‘easy way out’ type of things I’m trying to clean up as I work through this old code…so I had to rethink this approach. It really felt like there should be a way to grab it all in one query, I just needed to wrap my brain around it…and eventually I did get it with the following query:select distinct hometeam, (select top 1 weeknumber from nflschedule where weeknumber not in ( select weeknumber from nflschedule where hometeam = n1.hometeam or awayteam = n1.hometeam ) ) byeweek from nflschedule n1 order by byeweek, hometeam
The biggest catch here is that it only grabs the first bye week for a given NFL team (this is actually fine for now as each NFL team should only have one bye week a season in the current NFL system – remember know your data set and your data rules!). Anyway, munch on the query for a bit and I think you’ll see how the solution works it self out. In the meantime, peace out.This post has received 45 loves.
This is the personal blog of Kevin Marshall (a.k.a Falicon) where he often digs into side projects he's working on for digdownlabs.com and other random thoughts he's got on his mind.
Kevin has a day job as CTO of Veritonic and is spending nights & weekends hacking on Share Game Tape. You can also check out some of his open source code on GitHub or connect with him on Twitter @falicon or via email at kevin at falicon.com.
If you have comments, thoughts, or want to respond to something you see here I would encourage you to respond via a post on your own blog (and then let me know about the link via one of the routes mentioned above).