By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,301 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,301 IT Pros & Developers. It's quick & easy.

Finding Consecutive Records

100+
P: 102
There is a MS SQL Server table that has a Supplier Rating score in it. I have simplified it a bit. Here is how it is defined in SQL:
Expand|Select|Wrap|Line Numbers
  1. Column Name   Data Type
  2. -----------   ---------------
  3. id            int 
  4. supplier      varchar(100)
  5. fy            varchar(2)
  6. qtr           varchar(1)
  7. score         int 
  8.  
The data is as follows:
Expand|Select|Wrap|Line Numbers
  1. id  supplier          fy  qtr   score
  2. --- --------------    --  ---   -----
  3. 100 Doe Supply Co.    30  1     80
  4. 101 Smart Supply      30  1     100
  5. 102 Smart Supply      30  2     79
  6. 103 Doe Supply Co.    30  2     79 
  7. 104 Smart Supply      30  3     76
  8. 105 Doe Supply Co     30  3     70
  9. 106 Smart Supply      30  4     78 
  10. 107 Doe Supply        30  4     65  
  11.  
What I am wanting to do is write a SQL query that will find 2 consecutive table entries by supplier, FY and QTR whose scores are below 80. Here is what I want to see:

Expand|Select|Wrap|Line Numbers
  1. id  supplier          fy  qtr   score
  2. --- --------------    --  ---   -----
  3. 103 Doe Supply Co.    30  2     79
  4. 105 Doe Supply Co     30  3     70
  5. 103 Smart Supply      30  2     70
  6. 104 Smart Supply      30  3     76  
  7.  
I can write queries to read and update SQL but haven't a clue on how to do this.
Thanks in advance.
Apr 9 '17 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.