March 11, 2016 at 3:59 pm
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?
March 11, 2016 at 8:15 pm
create a calculated column in your T-SQL to evaluate to 1 or 0 based on your rules, and then sort by that. (Although the sort order in your report will override that.)
March 12, 2016 at 4:01 pm
would you show me the t-sql? I am not certain how to setup the sql?
March 12, 2016 at 4:20 pm
have you got the TSQL for your age calculations.....or is this part of your question?
some sample create table/insert data scripts and expected results will probably help
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 13, 2016 at 9:52 pm
TSQL is this part of my question
March 13, 2016 at 11:14 pm
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
.
Use GETDATE() to return the current date. (or Today() if you're using SSRS).
Maybe create a calculation for the "Jan 1 of the current school year"
a. get the current school year. I would assume it's something like Aug-May. If it's before June, then it's YEAR(TODAY()) otherwise it's YEAR(TODAY())+1.
b. Age for that is just DATEDIFF(year,...)
b(2). Just an IF statement (maybe IIF in SSRS)... or a CASE WHEN statement when you're using T-SQL.
just create a few calculated fields in your dataset and you're golden.
March 14, 2016 at 8:48 am
Would you show me the 'few calculated fields' in the dataset that I need in t-sql 2012?
March 14, 2016 at 9:28 am
sample data and expected results would help please.
students under 6 on 1st jan could be something like this
select yadda, yadda from whatever where yourdate <= dateadd(yy, datediff(yy, 0, getdate()) -5, 0)
as for students age 17.75......how are you defining this decimal portion of a date (months or days?)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 14, 2016 at 9:37 am
pietlinden (3/13/2016)
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
.
Use GETDATE() to return the current date. (or Today() if you're using SSRS).
Maybe create a calculation for the "Jan 1 of the current school year"
a. get the current school year. I would assume it's something like Aug-May. If it's before June, then it's YEAR(TODAY()) otherwise it's YEAR(TODAY())+1.
b. Age for that is just DATEDIFF(year,...)
b(2). Just an IF statement (maybe IIF in SSRS)... or a CASE WHEN statement when you're using T-SQL.
just create a few calculated fields in your dataset and you're golden.
Careful here, depending on the software school year could be the year the school year started in instead of ending in. I worked for a K-12 school district for 5 years and we had this issue between a couple of applications.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply