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:

enter image description here

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

enter image description here

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

@KevinBrydon Do you see that OP is edited and can you compare the times? — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 15, 2013 at 11:39

Steve Chambers · Accepted Answer · 2016-01-25 20:04:36Z

2

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.

edited Jan 25, 2016 at 20:04

answered Mar 15, 2013 at 11:45

Steve Chambers

39.8k29 gold badges179 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dis Shishkov · Accepted Answer · 2013-03-15 11:28:18Z

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

answered Mar 15, 2013 at 11:28

Dis Shishkov

6567 silver badges21 bronze badges

Comments

Indi · Accepted Answer · 2013-03-15 11:35:08Z

1

Using CTE and Row_number():

;with cte as (
   select Invoice, [Date], kms, row_number() over(order by [date]) rn
   from yourTable
)

select c1.[Date], c2.kms StartKms, c1.kms EndKms
from cte c1 left join cte c2 on c1.rn  = c2.rn +1
order by c1.[Date]

edited Mar 15, 2013 at 11:35

answered Mar 15, 2013 at 11:29

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Collectives™ on Stack Overflow

SQL Query - Filtered value from specific rows into result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related