Varshini S
asked on
SQL Query
I have the following tables.
Student
SequenceNumber CurrentQulificationStandar d Status Date
1 A-2000 Y 2012-03-18
2 A-2001 N 2013-08-19
3 B-3006 Y 2014-02-18
4 C-0009 Y 2014-12-09
StudentList
StudentId Qualified_Standard
123 A-2000
125 B-3006
121 A-2001
188 B-3006
118 B-3006
I need to find - given student has reached the CurrentQulificationStandar d or not ?
* Current Qualification standard is B-3006 . Since the date is <=GETDATE() AND MAX(Sequencenumber)=3 and STATUS=Y
In this above list three of them reached the current qualification standard
118 , 188 , 125
For achieving this I am using following query. Is this correct ? How do I optimize this ?
SELECT count(*) FROM StudentList WHERE StudentId =125 AND Qualified_Standard
IN (
SELECT CurrentQulificationStandar d FROM student WHERE status='Y' AND date<=GETDATE() AND SequenceNumber
IN (SELECT MAX(SequenceNumber) FROM student WHERE status='Y' AND date<=GETDATE() ))
Above query returns the count value to 1 so he is qualified
But student 123 is not qualified . so it will return 0
Is this correct ? How do I optimize this query ?
Student
SequenceNumber CurrentQulificationStandar
1 A-2000 Y 2012-03-18
2 A-2001 N 2013-08-19
3 B-3006 Y 2014-02-18
4 C-0009 Y 2014-12-09
StudentList
StudentId Qualified_Standard
123 A-2000
125 B-3006
121 A-2001
188 B-3006
118 B-3006
I need to find - given student has reached the CurrentQulificationStandar
* Current Qualification standard is B-3006 . Since the date is <=GETDATE() AND MAX(Sequencenumber)=3 and STATUS=Y
In this above list three of them reached the current qualification standard
118 , 188 , 125
For achieving this I am using following query. Is this correct ? How do I optimize this ?
SELECT count(*) FROM StudentList WHERE StudentId =125 AND Qualified_Standard
IN (
SELECT CurrentQulificationStandar
IN (SELECT MAX(SequenceNumber) FROM student WHERE status='Y' AND date<=GETDATE() ))
Above query returns the count value to 1 so he is qualified
But student 123 is not qualified . so it will return 0
Is this correct ? How do I optimize this query ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.