How to find the difference for a numeric field with 2 records?
How to find the difference for a numeric field with 2 records?
(OP)
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.
RE: How to find the difference for a numeric field with 2 records?
CODE
, 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