I like to post anything SQL that is "new" to me that perhaps I didn't know before or don't remember doing. Today was one such occasion.
I was challenged with the task of updating a table full of reading data for for our district's 3rd-5th graders. The data was comprised of a record per student per testing cycle, and was used for the purpose of tracking reading fluency. I have a second table which holds a score matrix letting me know per grade, per testing cycle what the low and high grades should be to determine if a student is above average, below average or at average.
This was something that I had to do as a "one time event" to about 7000 plus records. I decided that I could run a bunch of queries per reading cycle per grade to do this, but that seemed cheesy to me. I wanted to do it all in one fell swoop. Here is what worked for me.
SET dataTable.atAboveBelow =
WHEN dataTable.readingScore < (SELECT belowAvg FROM scoreMatrix where gradeLevel = dataTable.gradeLevel AND testWindow = dataTable.testWindow) THEN -1
WHEN dataTable.readingScore > (SELECT aboveAvg FROM scoreMatrix where gradeLevel = dataTable.gradeLevel AND testWindow = dataTable.testWindow) THEN 1