Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Getting the Consecutive records

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
Jeyakumar_mcp
Asked:
Jeyakumar_mcp
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
clarify, please:
sql 2000 or sql 2005+?
0
 
Jeyakumar_mcpAuthor Commented:
it is in sql 2000
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jeyakumar_mcpAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Jeyakumar_mcpAuthor Commented:
Thanks Angel. It works fine  with slight modification. Thanks a lot
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now