Posted on 2014-03-12
I have the following tables.
SequenceNumber CurrentQulificationStandard 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
I need to find - given student has reached the CurrentQulificationStandard 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
SELECT CurrentQulificationStandard 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 ?