In t-sql 2012, I have the following select statement currently;
SELECT PERNUM , RTRIM(LASTNAME) + ', ' + RTRIM(FIRSTNAME) + CASE WHEN MIDDLENAME IS NULL THEN '' WHEN RTRIM(MIDDLENAME) = '' THEN '' ELSE ' ' + SUBSTRING(RTRIM(MIDDLENAME), 1, 1) END AS STUDENTNAME GRADE, LANG ,BIRTHDATE
order by BIRTHDATE, LANG, PERNUM
What I need to do is a sort based upon the birthdate, lang, and pernum.
Basically the sort for birthdate should meet the following criteria:
a. If a student is under 6 years of age by Jan. 1 of the current school year or a student is currently 17.75 years of age or older (when sql is executed-today) they are to appear at the bottom of the sort order,
b. If a student is at least 6 years of age by Jan. 1 of the current school year or a student is currently under the age of 17.75 years of age (when sql is executed-today), they are to appear at the top of the sort order.
Thus would you show me the t-sql 2012 that will meet my requirements?