Change t-sql 2012 to not use a cte

Transact-SQL
1

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;

2

Try putting a ";" in front of WITH ?

; WITH [tbl_ASTU] 
AS 
(
...
3

I haven't looked at your cod closely, but if that still doesn't work you could try:

SELECT 
    CASE 
        WHEN Age_By_Jan01_CurrentYear ...

    ... full SELECT statement from your main SQL statement here ...

FROM
(
    SELECT PERNUM ,
                  RTRIM(LASTNAME) + ', ' + ...

    ... full SQL from your CTE here ...

) AS [tbl_ASTU]
ORDER BY 1 DESC;
4

Had you commented on my reply here, we might have tweaked it to accommodate your new needs.

2 Likes
5

that solved the issue. Thanks!