Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL Query

Posted on 2014-03-12
Medium Priority
127 Views
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
Question by:Varshini S
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
1 Comment

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39922961
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
)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month8 days, 22 hours left to enroll