quote:

---

Originally posted by tkizer

I don't understand the calculation. How did you get 10 for 01/02 and 01/03?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---

with cte
  as (select *
            ,row_number() over(partition by PlateNumber order by DepartureDate,JourneyID) as rn
        from Journeys
     )
select a.PlateNumber
      ,a.DepartureDate
      ,a.DepartureKM
      ,a.ArrivalDate
      ,a.ArrivalKM
      ,isnull(a.DepartureKM-b.ArrivalKM,0) as KMDifference
  from cte as a
       left outer join cte as b
                    on b.PlateNumber=a.PlateNumber
                   and b.rn=a.rn-1
 order by a.PlateNumber
         ,a.rn

quote:

---

Originally posted by bitsmed

Try this:

with cte
  as (select *
            ,row_number() over(partition by PlateNumber order by DepartureDate,JourneyID) as rn
        from Journeys
     )
select a.PlateNumber
      ,a.DepartureDate
      ,a.DepartureKM
      ,a.ArrivalDate
      ,a.ArrivalKM
      ,isnull(a.DepartureKM-b.ArrivalKM,0) as KMDifference
  from cte as a
       left outer join cte as b
                    on b.PlateNumber=a.PlateNumber
                   and b.rn=a.rn-1
 order by a.PlateNumber
         ,a.rn

However difference between journeyid 3 and 4 is -43 km (vehicle going reverse?).
Also the departure km on journeyid 6 and 7 from your sample data described here, is swapped in your link (sqlfiddle).

---