I have tables that record when certain items were sent or returned to a particular location, and I want to work out the intervals between each time a particular item is returned.

Sample data:

```
Item   ReturnDate:
Item1, 20120101
Item1, 20120201
Item1, 20120301
Item2, 20120401
Item2, 20120601

```

So in this case, we can see that the there was a month gap until Item 1 was returned the first time, and another month before it was returned the second time. Item 2 came back after 2 months.

My starting point is:

```
Select r1.Item, r1.ReturnDate, r2.Item, r2.ReturnDate, DateDiff(m, r1.ReturnDate, r2.ReturnDate)
from Returns r1
inner join Returns r2 on r2.VehicleNo = r1.VehicleNo

```

However, in the this sample, each item is compared to every other instance where it has been returned - and not just the next one. So I need to limit this query so it will only compare adjacent returns.

One solution is to tag each return with an count (of the number of times that item has been returned):

```
Item   ReturnDate, ReturnNo:
Item1, 20120101,   1
Item1, 20120201,   2
Item1, 20120301,   3
Item2, 20120401,   1
Item2, 20120601,   2

```

This would enable me to use the following T-SQL (or similar):

```
Select r1.Item, r1.ReturnDate, r2.Item, r2.ReturnDate, DateDiff(m, r1.ReturnDate, r2.ReturnDate)
from Returns r1
inner join Returns r2 on r2.VehicleNo = r1.VehicleNo 
and (r1.ReturnNo + 1 = r2.ReturnNo)

```

My first question is whether the is a sensible/optimal approach or whether there is a better approach?

Secondly, what is the easiest/slickest means of calculating the ReturnNo?