Find two consecutive rows

Question

I'm trying to write a query that will pull back the two most recent rows from the Bill table where the Estimated flag is true. The catch is that these need to be consecutive bills.

To put it shortly, I need to enter a row in another table if a Bill has been estimated for the last two bill cycles.

I'd like to do this without a cursor, if possible, since I am working with a sizable amount of data and this has to run fairly often.

Edit

There is an AUTOINCREMENT(1,1) column on the table. Without giving away too much of the table structure, the table is essentially of the structure:

CREATE TABLE Bills (
  BillId INT AUTOINCREMENT(1,1,) PRIMARY KEY,
  Estimated BIT NOT NULL,
  InvoiceDate DATETIME NOT NULL
)

So you might have a set of results like:

BillId               AccountId            Estimated InvoiceDate
-------------------- -------------------- --------- -----------------------
1111196              1234567              1         2008-09-03 00:00:00.000
1111195              1234567              0         2008-08-06 00:00:00.000
1111194              1234567              0         2008-07-03 00:00:00.000
1111193              1234567              0         2008-06-04 00:00:00.000
1111192              1234567              1         2008-05-05 00:00:00.000
1111191              1234567              0         2008-04-04 00:00:00.000
1111190              1234567              1         2008-03-05 00:00:00.000
1111189              1234567              0         2008-02-05 00:00:00.000
1111188              1234567              1         2008-01-07 00:00:00.000
1111187              1234567              1         2007-12-04 00:00:00.000
1111186              1234567              0         2007-11-01 00:00:00.000
1111185              1234567              0         2007-10-01 00:00:00.000
1111184              1234567              1         2007-08-30 00:00:00.000
1111183              1234567              0         2007-08-01 00:00:00.000
1111182              1234567              1         2007-07-02 00:00:00.000
1111181              1234567              0         2007-06-01 00:00:00.000
1111180              1234567              1         2007-05-02 00:00:00.000
1111179              1234567              0         2007-03-30 00:00:00.000
1111178              1234567              1         2007-03-02 00:00:00.000
1111177              1234567              0         2007-02-01 00:00:00.000
1111176              1234567              1         2007-01-03 00:00:00.000
1111175              1234567              0         2006-11-29 00:00:00.000

In this case, only records 1111188 and 1111187 would be consecutive.

Answer 1

You should be able to do a descensing sorted query on estimated = true and select top 2. I am not the best at SQL so i cant give exact language structure

Answer 2

Do you have a column for "statement number", e.g., if Q12008 was statement 28 for a particular customer, then Q22008's bill would be 29, Q32008's bill would be 30 (assuming quarterly billing). You could then check that the statement numbers were adjacent rather than having to do date manipulation.

Answer 3

select top 2 * 
from bills
where estimated = 1 
order by billdate desc

Answer 4

Assuming the rows have sequential IDs, something like this may be what you're looking for:

select top 1 * 
from 
Bills b1
inner join Bills b2 on b1.id = b2.id - 1
where
b1.IsEstimate = 1 and b2.IsEstimate = 1
order by
b1.BillDate desc