0

I need to find the age of student when he was readmitted to a class. Now to do this I have one table student_history hence first needed to retrieve the "admin_date" admission date from student history when student status equals to 'RE-ADMIN're admitted. And then to check if admin_date is greater than admin_date of last Readmission 'RE-ADMIN' or Completed 'COMP' in records. Now the query is not working as it should be and running into different errors.

SELECT
    s.admin_date
FROM
    student_history s
WHERE
    student_st = 'RE-ADMIN'
    AND
    s.admin_date > (
        SELECT
            s.admin_date
        FROM
            s
        WHERE
            student_st IN('RE-ADMIN','COMP')
    )

couple of similar links researched.

Age Calculation Query comparison query taking ages

Community
  • 1
  • 1
user3583436
  • 1
  • 1
  • 1
    What error message is produced? It looks like the subquery in your where clause will produce more than one record, which will cause a problem. – James King Apr 29 '14 at 02:51
  • I seriously doubt you can figure out how old somebody is based solely on when you admitted them... you need a birthdate. Could we get some starting data and sample results? – Clockwork-Muse Apr 29 '14 at 03:13

2 Answers2

0

Change your WHERE subquery to this:

SELECT
    MAX(s.admin_date)
FROM
    student_history
WHERE
    student_id = @studentId
    AND
    student_st IN ( 'RE-ADMIN', 'COMP' )

You'll also need to add your student_id = @studentId predicate to the outerquery.

Dai
  • 141,631
  • 28
  • 261
  • 374
0

Dai is right you have to add student id for omitting the error. But by this you will get last re-admission date. how will you calculate his age with admission date. you need dob field for this.

arpan sharma
  • 64
  • 4