How to find the difference for a numeric field with 2 records?

[sqlguy1]

Is there a way in SQL to find the difference for a numeric field between the current record and the previous record? For example, a table has a field called InvoiceNbr. The currect record has InvoiceNbr = 1500. The previous record has the same InvoiceNbr field = 1000 so the difference would be 500. I would like to run a query that shows the previous InvoiceNbr, current InvoiceNbr, and the differnce between the two if the difference between the two records is greater than one. Thanks.

 

[r937]

SELECT this.InvoiceNbr AS this_number
     , prev.InvoiceNbr AS prev_number
  FROM daTable AS this
LEFT OUTER
  JOIN daTable AS prev
    ON prev.InvoiceNbr =
       ( SELECT MAX(InvoiceNbr)
           FROM daTable
          WHERE InvoiceNbr < this.InvoiceNbr )
 WHERE this.InvoiceNbr - prev.InvoiceNbr > 1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon