SQLTeam.com | Weblogs | Forums

T-sql 2012 sort order


#1

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
FROM ASTU
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?


#2

Please define "current school year".


#3

Current school year is 2015-2016. Next school year is 2016 to 2017. Does that answer your question ?


#4

Almost - can you give med start and end date?


#5

med start is 8/1/2015 and med end date is 7/31/2016


Change t-sql 2012 to not use a cte
#6

I think this will come close to what you want:

select pernum
      ,rtrim(lastname)
      +', '
      +rtrim(firstname)
      +rtrim(left(isnull(middlename,''),1))
       as studentname
      ,grade
      ,lang
      ,birthdate
  from astu
 order by case
             when year(dateadd(month,-7,current_timestamp))-year(birthdate)<6
               or datediff(day,birthdate,current_timestamp)/365.25>=17.75
             then 1
             else 0
          end
         ,birthdate
         ,lang
         ,pernum
;