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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to find the difference for a numeric field with 2 records?
- Thread starter sqlguy1
- Start date
Code:
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 > 1r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
- Status
Similar threads
- Locked
- Question
- Locked
- Question