Solved

Getting the Consecutive records

Posted on 2008-10-21
6
215 Views
Last Modified: 2010-04-21
Hi All,
 I have table with colum Id (Identity column) and return .
The table data look like below
ID    Returns
1     0.0
2     1.2
3.    0.0
4     0.0
5    0.0
6   2.3
7  0.0
8  0.0
9  3.4

In the above table, I only want the record which have Returns as zero for three consecutively rows.
In the above table, i need a query i will select only the record 3,4 and 5.I dont want to use loops. Please help me.
0
Comment
Question by:Jeyakumar_mcp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22767081
clarify, please:
sql 2000 or sql 2005+?
0
 
LVL 1

Author Comment

by:Jeyakumar_mcp
ID: 22767192
it is in sql 2000
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 22767372
ok, what about this:
select t1.id, t2.id, t3.id
  from yourtable t1
  join yourtable t2
    on t2.id = t1.id + 1
   and t2.returns = t1.returns
  join yourtable t3
    on t3.id = t3.id + 1
   and t3.returns = t1.returns
  where t1.returns = 0.0

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:Jeyakumar_mcp
ID: 22767562
HI,
  It is not working.
For the input
The table data look like below
ID    Returns
1     0.0
2     1.2
3.    0.0
4     0.0
5    0.0
6   2.3
7  0.0
8  0.0
9  3.4

I need the output as below
ID    Returns
3.    0.0
4     0.0
5    0.0

Please advice
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22767799
does my query return 1 rows with the 3 id values?

if yes:
select t.* 
  from yourtable t
  join ( select t1.id id1, t2.id id2, t3.id id3
  from yourtable t1
  join yourtable t2
    on t2.id = t1.id + 1
   and t2.returns = t1.returns
  join yourtable t3
    on t3.id = t3.id + 1
   and t3.returns = t1.returns
  where t1.returns = 0.0
  ) sq
  on t.id in (sq.id1, sq.id2, sq.id3)

Open in new window

0
 
LVL 1

Author Closing Comment

by:Jeyakumar_mcp
ID: 31508259
Thanks Angel. It works fine  with slight modification. Thanks a lot
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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