Add Former English Learner from PowerSchool Into Illuminate

Our English Language Development department wants to track students who are considered “former English learners” in Illuminate.  The criterion is if they’ve been exited from the EL program within the last four years.  This requires a few preparations.

First, set the code within Illuminate to display the former EL status.  I just chose the next one available.  I’ll use this variable in my SQL script, which comes next.

From the cog, choose Code Management

Look for English Proficiency.  I just start typing “english,” and it appears:

Then add the new code.  In our case, plain number 6 was the next available one.  I have to remember this value:

Next, I need to edit studemo.sql on the server where the SQL extract scripts are installed.  These are the scripts that build queries from the PowerSchool database, assemble the files, and then SFTPs them to Illuminate’s server.

The column that provides English proficiency status is column 14.  For that column, I use a CASE statement:

   when (sc.flaglep = 1) then 3
   when (sc.lepexitdate > to_date('6/15/'||to_char(extract(year from sysdate)-4),'mm/dd/yyyy')) then 6
else null end

The prefix sc refers to an alias for our state reporting code.  PowerSchool users outside of Michigan may have different column names.  Walking through this CASE statement, here’s what I’m saying:

  • If the “is an English learner” checkbox is marked, the value is 1 in the database, so I want to return the value of 3 to send to Illuminate (note in the screen shot above of the code table, where 3 indicates “English Learner”).
  • If the EL exit date is greater than a date of June 15 of the year that’s 4 earlier than the current system date, then return a 6.
  • Otherwise, return nothing

So the possible output values will be 3, 6, or nothing.  This is proven when looking at studemo.txt in Excel:

Once uploaded to Illuminate, this value can then be used in filters for reports, student groups, and assessments:

Clearing a Remembered Login from SQL Server Management Studio

Dating back to the olden days of earlier versions of SSMS, it likes to remember the login name of the last new user, even if that user has since been deleted.  While it’s not a functional deal-breaker in using SSMS, it’s just plain ol’ annoying.

The information is stored in this file (naturally, I’m using Windows 10 here):

%AppData%\Microsoft\SQL Server Management Studio\[version]\SqlStudio.bin

I renamed the existing SqlStudio.bin to SqlStudio.bin.old and the next time I launched SSMS, it recreated a new SqlStudio.bin:

The only downside, of course, is that it blows out everything — saved server connections and every other login detail.  But that shouldn’t be a big deal because if you have many servers and many different logins, you should have all of that stored safely in a secure repository elsewhere, right?

I mean, nobody would be silly enough to use the same simple password for “sa” for every SQL server they manage, right?  Right?

Convert Names from ALL CAPS (or all lower) to Mixed Case in SQL

Preface: use LENGTH for MySQL, LEN for Transact-SQL (SQL Server). I copied the following code from one of my MySQL instances. Couldn’t figure out initially why LEN wasn’t working, before I remembered that I was still thinking in Transact-SQL instead of MySQL. Yay for industry standards! </sarcasm>

   lower(right(first_name, length(first_name) - 1))) as [whateverAlias],
   lower(right(last_name, length(last_name)-1))) as [whateverAlias]

upper(left(first_name,1)) grabs the first character of your first_name field and changes it to upper case.

lower(right(first_name, length(first_name)-1)): Taking the total length of the first_name field, then subtracting 1, go from the right that many characters, since we’re skipping the first character. Change that to lower case. Remember to use LEN instead of LENGTH in Transact-SQL

concat (….) as whatever: concatenates the upper case letter and lower case remainder into a new whatever value that’s more easy to remember than upper(left(first……).