473,396 Members | 1,757 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Finding Consecutive Records

102 64KB
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
0 1430

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

Similar topics

5
by: Mike | last post by:
Hi I've written a module to find the first occurrence of a record in a query (that is my forms datasource) that matches four specified criteria. No matter what I try it doesn't seem to find ...
2
by: Carroll | last post by:
I'm looking for a way in SQL to find duplicate records in a single table, that are the same based on 3 columns, regardless of what is in the other columns in the duplicate records. I would like to...
1
by: rgurganus | last post by:
I'm trying to setup a way to find possible duplicate or near-duplicate records of contact people. I have a table of 50k records doing an inner join with itself, something like this: SELECT...
6
by: mservice | last post by:
Hi there, newbie to Access, I would normally use cursors in SQL Plus to do this type of thing, however I have been tasked with modifying an access 2000 application and am stuck on the VB code/query...
2
by: NazishKanwal | last post by:
Hi all I have a table in which I save a record each time when a user checks in and checks out the office through a time in/ time out device(this device takes thumb impression of user, thus ...
4
by: Thomas Arthur Seidel | last post by:
Hello to all, I have a small or big problem with a customer data base, where during a change of system we might have created duplicate records. This should be easy to find, you might think, but,...
1
by: Rantoun | last post by:
I know what I am asking for is pretty hard, but still maybe there is someone out there who is able to solve my problem! I have a table that look like this CREATE TABLE VEHICLETRIPS ( VID...
1
by: mabubakarpk | last post by:
Hello every one, I Have table e.g EmpID TDate Status *************************************** 1 01-01-08 AB 1 02-01-08 AB 1 ...
5
by: kimmer | last post by:
I have a database that records results from devices that uses an algorthmic code as its meter. I capture from and to readings. I would like to compare readings between 2 consecutive records to see...
9
by: Calvin Dent | last post by:
Hey Guys, Currently I'm using a code to find all records before a certain date. I'm using If LogDate < Date -5 Then The problem with this is, Its only filtering by Day not month or year. ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.