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

x
Solved

# Help with query

Posted on 2011-10-13
Medium Priority
230 Views
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 :

0
Question by:Baxters801

LVL 143

Expert Comment

ID: 36962549
I presume you want to have something like this:

select t.*
from yourtable t
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 )
0

LVL 4

Expert Comment

ID: 36962557
Hi,

What output your expecting.can you give detail.

Regards
Anand
0

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 *
FROM
(
JOIN StudentGrade t2 ON t1.sid = t2.sid AND t1.date <> t2.date
WHERE
AND DATEDIFF(year, date, secondDate) < 2
``````

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"?
0

Author Comment

ID: 36962804
STrickd,

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

0

Author Comment

ID: 36963446
Hope someone can help me....
0

LVL 143

Expert Comment

ID: 36966903
yes, if you explain with data samples, eventually
0

LVL 70

Expert Comment

ID: 36970977
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
WHERE

0

## Featured Post

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.…
###### Suggested Courses
Course of the Month20 days, 7 hours left to enroll