• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

SQL Query

I have the following tables.

Student

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

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 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
IN (
        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 ?
0
Varshini S
Asked:
Varshini S
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's give this a try to return "all" the students that are "qualified standard"
 SELECT sl.StudentId  
   FROM StudentList sl
   WHERE sl.Qualified_Standard = 
  ( SELECT TOP 1 st.CurrentQulificationStandard    
          FROM Student st
        WHERE st.status='Y' 
           AND st.date <= GETDATE()
        ORDER BY st.SequenceNumber   DESC
  )

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now