Geek Answers Handbook

Find two consecutive lines

I am trying to write a query that will cancel the last two rows from the Bill table, where the "Bottom" flag is true. The trick is that they must be sequential scores.

In short, I need to enter a row in another table if Bill has been evaluated for the last two counting cycles.

I would like to do this without a cursor, if possible, since I work with a lot of data, and this should be done quite often.

Edit

There is an AUTOINCREMENT column (1,1) in the table. Without highlighting too much table structure, the table essentially refers to the structure:


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

So you can have many results, such as:

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

1111188 1111187 .

+5
4

, , - , :

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
+11
select top 2 * 
from bills
where estimated = 1 
order by billdate desc
+2

= true 2. SQL,

0

You have a column for “operator number”, for example, if Q12008 was an expression of 28 for a particular customer, then account Q22008 will be 29, account Q32008 will be 30 (assuming quarterly billing). You can then verify that the operator numbers are contiguous, rather than doing date manipulations.

0
source

More articles:


All Articles