Getting Student Age using sqlReports

Forums:

Hello all, I've been trying to find a way of using the ^(age) dat in sqlReports, and of course I wasn't able to do so... which prompted me to find this bit of code to return a student's age based on s.dob (where s. indicates students table). This is one of my "selects" at the beginning of the query:

TRUNC(
MONTHS_BETWEEN(
SYSDATE,
CASE TO_CHAR(S.DOB,'MMDD')
WHEN '0229'
THEN CASE TO_CHAR(TRUNC(SYSDATE,'YYYY') + 59,'MMDD')
WHEN '0229'
THEN S.DOB
ELSE S.DOB - 1
END
ELSE S.DOB
END
) / 12
)

I admit freely that I've borrowed this code from the results of a search on the Oracle website, and that I've climbed up on the shoulders of people far more advanced than myself to make it work.

I'm curious to know if sqlReports has something native in it that would make this all unnecessary? If not, could somebody please help me figure out how on earth to add to this snippet to return months as well as years?

Thanks all,
Schelly

SELECT s.lastfirst, s.dob, Trunc( (sysdate - s.dob) / 365.25) AS age
FROM students s

If you wanted number of months then remove the Trunc function.

Michael P. Moore
Student Information System Administrator
South Brunswick Public Schools
South Brunswick, NJ 08852

How can I get this SQL query to work in Custom SQL Reports? It works fine in SQL Developer.
select students.lastfirst,
--change dates to 8/14/2012
sum(case when (att_date<'9-nov-12' and att_date>'9-aug-12' and att_code='UNV') then 1 else 0 end) as "12-13 T1 UNV",
sum(case when (att_date<'9-nov-12' and att_date>'9-aug-12' and att_code='UNX') then 1 else 0 end) as "12-13 T1 UNX",
sum(case when (att_date<'9-nov-12' and att_date>'9-aug-12' and att_code='EXC') then 1 else 0 end) as "12-13 T1 EXC",
sum(case when (att_date<'9-nov-12' and att_date>'9-aug-12' and att_code='SUS') then 1 else 0 end) as "12-13 T1 SUS",
sum(case when (att_date>'9-nov-12' and att_date<'22-feb-13' and att_code='UNV') then 1 else 0 end) as "12-13 T2 UNV",
sum(case when (att_date>'9-nov-12' and att_date<'22-feb-13' and att_code='UNX') then 1 else 0 end) as "12-13 T2 UNX",
sum(case when (att_date>'9-nov-12' and att_date<'22-feb-13' and att_code='EXC') then 1 else 0 end) as "12-13 T2 EXC",
sum(case when (att_date>'9-nov-12' and att_date<'22-feb-13' and att_code='SUS') then 1 else 0 end) as "12-13 T2 SUS",
sum(case when (att_date>'22-feb-13' and att_date<'6-jun-13' and att_code='UNV') then 1 else 0 end) as "12-13 T3 UNV",
sum(case when (att_date>'22-feb-13' and att_date<'6-jun-13' and att_code='UNX') then 1 else 0 end) as "12-13 T3 UNX",
sum(case when (att_date>'22-feb-13' and att_date<'6-jun-13' and att_code='EXC') then 1 else 0 end) as "12-13 T3 EXC",
sum(case when (att_date>'22-feb-13' and att_date<'6-jun-13' and att_code='SUS') then 1 else 0 end) as "12-13 T3 SUS"
from attendance
join students on students.id =attendance.studentid
join attendance_code
on attendance.attendance_codeid=attendance_code.id
--join cc on attendance.studentid=cc.studentid
where ((attendance_code.att_code='UNV') or (attendance_code.att_code='UNX'))
and enroll_status=0
and grade_level<6
--and cc.course_number='TACCG04610'
--and students.schoolid=~(curschoolid)
group by students.lastfirst
order by students.lastfirst

First thing that needs to be done is to remove the --. It looks like you're trying to comment out something but that won't work in the actual tlist_sql. The next thing I'd do is change your dates to actual dates. to_date('22-feb-13','dd-mon-yy')

Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org

I tried a shortened version:
select students.lastfirst,
sum(case when att_date to_date('09-nov-12','dd-mon-yy') and att_date to_date('09-aug-12','dd-mon-yy') and att_code = 'UNV' then 1 else 0 end) as "12-13 T1 UNV"
from attendance
join students on students.id =attendance.studentid
join attendance_code
on attendance.attendance_codeid=attendance_code.id
and enroll_status=0
and grade_level<6
group by students.lastfirst
order by students.lastfirst

This didn't work either.

This would probably work better if you used:

sum(case when att_date = to_date('09-nov-12','dd-mon-yy') and att_date = to_date('09-aug-12','dd-mon-yy') and att_code = 'UNV' then 1 else 0 end) as "12-13 T1 UNV"

Subscribe to Comments for "Getting Student Age using sqlReports"