Start data to be equal to End data of previous record? 1

[Sanasta]

Hi,
I have a problem. I would like data in a field KmEnd to be equal to KmStart in a previous record for the Same RegNo. (RegNo is not a primary key). I have no idea how to do this.
Kindly please help me.
Sanasta

 

[ddiamond]

Sanasta,

Sorry to here you're sick. Hope you get better soon.

The "not exists" clause is necessary, otherwise you will return too many results. The purpose of the "not exists" clause was to eliminate all records except those that are right next to each other. In other words, look at all records where record b is less than record a and there does not exist any records between them. There was however a small flaw in my logic. The between operator will not work as intended. Here is the fix:

select a.KmEnd as CurrentMileage, b.KmEnd as PreviousMileage
from RouteAvtovozi a Inner Join RouteAvtovozi b on
  a.RegNo = b.RegNo
where a.KomNo = <Current KomNo>
  and b.KomNo < a.KomNo
  and not exists (
    select * from RouteAvtovozi c
    where c.RegNo = a.RegNo 
      and c.KomNo > b.KomNo 
      and c.KomNo < a.KomNo)

Feel free to remove "a.KomNo = <Current KomNo>" if you want to return everything. Hope this helps.

 

[Sanasta]

DDiamond,
I simply have no words to explain my gratitude to you for help.
It works perfect. I understand your idea right the first time you suggest it. But I was no able to realise it because I am not so clever as you.
Now I have to apply it in real program situation when go to work but I think that there will not be any problems.
Thanks
Sanasta