I would like to change the following t-sql 2012 from a cte to sql that does not need a cte. I would like to change the t-sql 2012 since the following t-sql with
a cte does not work in a vb.net 2010 application.
The sql meets the following requirment:
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 how to accomplish my goal?
WITH [tbl_ASTU]
AS
(
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 ,
DATEDIFF(DAY, BIRTHDATE, CONCAT( YEAR(GETDATE()) , '-01' , '-01') ) / (365.23076923074) Age_By_Jan01_CurrentYear,
DATEDIFF(DAY, BIRTHDATE, GETDATE() ) / (365.23076923074) Age_By_Today
FROM ASTU
)
SELECT
CASE
WHEN Age_By_Jan01_CurrentYear >= 6.00 THEN 1 /*student is at least 6 years of age by Jan. 1 of the current school year - appear at the top */
WHEN Age_By_Today < 17.75 THEN 1 /*student is currently under the age of 17.75 years of age - appear at the top */
WHEN Age_By_Jan01_CurrentYear < 6.00 THEN 0 /*student is under 6 years of age by Jan. 1 of the current school year - appear at the bottom */
WHEN Age_By_Today >= 17.75 THEN 0 /*student is currently 17.75 years of age or older - appear at the bottom /
ELSE NULL
END ,
FROM [tbl_ASTU]
ORDER BY 1 DESC;