March 16, 2016 at 4:44 pm
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;
March 16, 2016 at 4:49 pm
wendy elizabeth (3/16/2016)
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 witha 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;
To answer your question... just move the code from the CTE to a derived table in the from clause of the outer SELECT.
Shifting gears, the original code should work just fine in a "a vb.net 2010 application" because CTEs have been out since the RTM of 2005. Something else is wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 5:12 pm
The query may be greatly simplified.
You just need to start from another end.
Do not turn birthday to ages, turn predefined ages into date ranges for birthdays:
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-6, 0) FromDate, GETDATE() ToDate, 'Too Young on 1st Jan' PeriodName, 2 Priority
UNION
SELECT dateadd (mm, -(17*12+9), GETDATE()) FromDate, DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-6, 0) ToDate, 'School Age' PeriodName, 1 Priority
UNION
SELECT CONVERT (DATETIME, 0) FromDate, DATEADD (mm, -(17*12+9), GETDATE()) ToDate, 'Too old for School' PeriodName, 2 Priority
Then it should be obvious how to use these ranges to select you student records:
SELECT *
FROM (
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-6, 0) FromDate, GETDATE() ToDate, 'Too Young on 1st Jan' PeriodName, 2 Priority
UNION
SELECT dateadd (mm, -(17*12+9), GETDATE()) FromDate, DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-6, 0) ToDate, 'School Age' PeriodName, 1 Priority
UNION
SELECT CONVERT (DATETIME, 0) FromDate, DATEADD (mm, -(17*12+9), GETDATE()) ToDate, 'Too old for School' PeriodName, 2 Priority
) P (BirthdayFromDate, BirthdayToDate, PeriodName, SelectionPriority)
INNER JOIN ASTU ON ASTU.BIRTHDATE >= P.BirthdayFromDate AND astu.BIRTHDATE < P.BirthdayToDate
ORDER BY P.SelectionPriority
_____________
Code for TallyGenerator
March 16, 2016 at 6:33 pm
Sergiy (3/16/2016)
Do not turn birthday to ages, turn predefined ages into date ranges for birthdays:
Best idea I've heard in a month.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 6:37 pm
However you approach it, the query should work fine from .NET. If you want to keep the query code in the database, turn it into a stored procedure and then have the application call the stored procedure. You can get back your Data.DataTable and you're off to the races.
March 17, 2016 at 3:02 pm
wendy elizabeth (3/16/2016)
I would like to change the t-sql 2012 since the following t-sql witha cte does not work in a vb.net 2010 application.
Set up an extended events session on your test server, then run the VB.Net 2010 application with the CTE and capture the commands it sends.
My guess is that you will see your query embedded in a batch, and the statement before it not properly semicolon-terminated.
If that it true, then the next step is to find out who is responsible and repeatedly hit them over the head with a rubber bat until they get their act together and fix the code to use statement terminators in the submitted SQL.
March 17, 2016 at 5:28 pm
Hopefully, it's one of those really, really hard rubber bats with a steel core. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply