Finding Consecutive Records

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • sc5502
    New Member
    • Jun 2014
    • 102

    Finding Consecutive Records

    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:
    Code:
    Column Name   Data Type
    -----------   ---------------
    id            int 
    supplier      varchar(100)
    fy            varchar(2)
    qtr           varchar(1)
    score         int
    The data is as follows:
    Code:
    id  supplier          fy  qtr   score
    --- --------------    --  ---   -----
    100 Doe Supply Co.    30  1     80
    101 Smart Supply      30  1     100
    102 Smart Supply      30  2     79
    103 Doe Supply Co.    30  2     79 
    104 Smart Supply      30  3     76
    105 Doe Supply Co     30  3     70
    106 Smart Supply      30  4     78 
    107 Doe Supply        30  4     65
    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:

    Code:
    id  supplier          fy  qtr   score
    --- --------------    --  ---   -----
    103 Doe Supply Co.    30  2     79
    105 Doe Supply Co     30  3     70
    103 Smart Supply      30  2     70
    104 Smart Supply      30  3     76
    I can write queries to read and update SQL but haven't a clue on how to do this.
    Thanks in advance.
Working...