Using decode with to_date

Question

If anyone could help me with the following, I'd be grateful: I'm trying to write a code (create an alert in PowerSchool) that will indicate if a student is younger or older than average for their current grade level. (For example, as student born before 6/30/2002 is older than average for 9th grade) I can't seem to make DECODE work in conjunction with >= TO_DATE. Here's my statement:

select lastfirst, decode  (dob >=  to_date ('2002-06-30', 'yyyy-mm-dd'), 'old') DOB
from students
where grade_level = 9
order by lastfirst

Answer 1

You probably could get away with using a PowerSchool decode in your sql, but I find code easier to write when fewer languages/systems are included, so I would leave it out. I would also let SQL do the average age calculation so you don't have to supply or calculate average dates yourself (see Averaging dates in oracle sql for an explanation of the TO_DATE line).

SELECT
    LastFirst AS "Student Name"
    ,CASE WHEN dob >= (
        SELECT
            TO_DATE(ROUND(AVG(TO_NUMBER(TO_CHAR(dob, 'J')))),'J')
        FROM Students
        WHERE Grade_Level = 9
        AND Enroll_Status = 0
    ) THEN 'Younger' ELSE 'Older' END AS "Older or Younger?"
FROM Students
WHERE Grade_Level = 9
ORDER BY LastFirst

The subquery calculates the average birthday, and the CASE statement compares each of your records against that, reporting if it's older or younger.

In the subquery that calculates the average, I've taken the liberty of assuming you only want to compare against currently enrolled students, since withdrawn students retain the same grade level. You don't really want those students who left 10 years ago and are still listed as a 9th grader to mess with your average numbers.