Help with query

Posted on 2011-10-13
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 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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 > and <= dateadd(year, 1, and o.grade < 80 )
    LVL 4

    Expert Comment


    What output your expecting.can you give detail.

    LVL 28

    Accepted Solution

    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 * 
       SELECT t1.sid,, t1.grade, t2.grade as secondGrade, As secondDate
       FROM StudentGrade t1
       JOIN StudentGrade t2 ON t1.sid = t2.sid AND <>
    ) 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"?

    Author Comment


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


    Author Comment

    Hope someone can help me....
    LVL 142

    Expert Comment

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

    Expert Comment

    Something like this should do what you need:

    ;WITH CTE AS (
    SELECT sid, date, grade, ROW_NUMBER() OVER (PARTITION BY sid ORDER BY date) AS row_num
    FROM table_name
    SELECT sid
    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now