A quick SQL challenge...

Yes I’m back. I would have posted yesterday but I was just too tired from getting up at 4:30 am to drive all over NJ and NY before going to work (for 14+ hours). Anyway, I do have a few interesting things to share tonight: 1. fubnub.com now has support for fubnub'ing your del.icio.us RSS feed. De.licio.us has some auto-posting features of their own (that I have never played with), so I’m not sure if fubnub really adds anything to what you can already do…but I still thought it was worth the small time it took me to set up…and if nothing else, I like the fact that you can really customize the template and the data that you fubnub (again I don’t know what delicious lets you do, but I’m guessing it’s not quite as free form as fubnub is). 2. I’m also working through some really old DW code trying to bring it back to life a little…as such I’m getting to see how bad of a developer I really was (and probably still am)…tonight I hit a page that needed to show bye weeks for NFL teams. I have a table that holds the NFL schedule, and so basically what I needed to do was pull out a list of the teams/weeks that don’t have records…here’s my basic table structure (for the things that matter in this challenge):

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 41 loves.


ARCHIVE OF POSTS
see more archived posts »


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