[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Help with query

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-06-21
I am not quite sure how to go about creating this query..These values are all from one table...

I need to find two consecutive grades that are < 90 and < 80 OR Grade <= 80 OR 2nd grade <= 90 and < 89 and these values must be consecutive and within a two year period.

The table is :

sid date   grade
Question by:Baxters801
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36962549
I presume you want to have something like this:

select t.*
  from yourtable t
 where t.grade < 90
   and exists ( select null from yourtable o where o.sid = t.sid and o.date > t.date and o.date <= dateadd(year, 1, t.date) and o.grade < 80 )

Expert Comment

ID: 36962557

What output your expecting.can you give detail.

LVL 28

Accepted Solution

strickdd earned 2000 total points
ID: 36962613
I am assuming "sid" is the student id and therefore can have multiple records for a single student and the data being pulled is in reference student by student. If that is the case:

   SELECT t1.sid, t1.date, t1.grade, t2.grade as secondGrade, t2.date As secondDate
   FROM StudentGrade t1
   JOIN StudentGrade t2 ON t1.sid = t2.sid AND t1.date <> t2.date
) studentGrades
    (grade < 90 AND secondGrade < 80)
    OR (grade <= 80)
    OR (secondGrade <= 90 AND secondGrade < 89)
    AND DATEDIFF(year, date, secondDate) < 2

Open in new window

This is what you want base on what you described, but a few things don't make sense.

"two consecutive grades that are < 90 and < 80"
   - This will only check that both grades are less then 80 because 80-89 values are less than 90, but aren't less than 80 and the "and" limits it

"2nd grade <= 90 and < 89"
   - This will only find a second grade the is less than 89, similar to reason above

"values must be consecutive"
   - Not sure what you mean by this, what makes a grade "consecutive"?
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 36962804

Sorry I meant < 90 or > 80
Consecutive means that two grades in a row by date meet the criteria


Author Comment

ID: 36963446
Hope someone can help me....
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36966903
yes, if you explain with data samples, eventually
LVL 70

Expert Comment

by:Scott Pletcher
ID: 36970977
Something like this should do what you need:

SELECT sid, date, grade, ROW_NUMBER() OVER (PARTITION BY sid ORDER BY date) AS row_num
FROM table_name
FROM CTE AS grade1
INNER JOIN CTE AS grade2 ON grade2.sid = grade1.sid AND grade2.row_num = grade1.row_num + 1
      ( grade1.grade <<value(s) to match first grade>> AND grade2.grade <<value(s) to match first grade>> )


Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question