SQL Query - Filtered value from specific rows into result

Question

I have a table where I store invoices of spent fuel and the km's where the car was refueled, with the following structure:

My goal is to obtain a result like the following, so I can calculate the spent km's beetween invoices.

Any advice regarding how I can structure the query to get the desired result?

Answer 1

SELECT Date,
       (SELECT MAX(Kms) FROM invoices i2 WHERE i2.Kms < i1.Kms) AS StartKm,
        Kms AS FinishKm
FROM invoices i1
ORDER BY Kms

See: SQL Fiddle Demo.

Answer 2

;WITH Invoices AS 
(
    SELECT 456 AS Invoice, '2013-03-01' AS [Date], 145000 AS Kms
    UNION ALL
    SELECT 658 AS Invoice, '2013-03-04' AS [Date], 145618 AS Kms
    UNION ALL
    SELECT 756 AS Invoice, '2013-03-06' AS [Date], 146234 AS Kms
), OrderedInvoices AS
(
    SELECT Invoice, [Date], Kms, ROW_NUMBER() OVER(ORDER BY [Date]) AS RowNum
    FROM Invoices
)

SELECT i1.[Date], i2.Kms AS StartKms, i1.Kms AS FinishKms
FROM OrderedInvoices AS i1
LEFT JOIN OrderedInvoices AS i2
    ON i1.RowNum = i2.RowNum + 1