StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFind two consecutive rows
text
Body
copied!<p>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. </p> <p>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.</p> <p>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.</p> <p><strong>Edit</strong></p> <p>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:</p> <pre><code> CREATE TABLE Bills ( BillId INT AUTOINCREMENT(1,1,) PRIMARY KEY, Estimated BIT NOT NULL, InvoiceDate DATETIME NOT NULL ) </code></pre> <p>So you might have a set of results like:</p> <pre> 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 </pre> <p>In this case, only records 1111188 and 1111187 would be consecutive.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload