Search code examples
sql-servert-sqlsequences

SQL Calculate (time) gap between occurrences in a log

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?

Solution

  • If you are using SQL Server 2005+, use ROW_NUMBER() to do exactly what you want:

    WITH RankedReturn AS
(
  SELECT Item, ReturnDate, 
    ROW_NUMBER() OVER (PARTITION BY Item ORDER BY ReturnDate DESC) AS ReturnNo
  FROM Returns
)
SELECT * FROM RankedReturn

    Obviously, now that you have your CTE you can put whatever you need in the outer SELECT. I would use an OUTER APPLY for this:

    WITH RankedReturn AS
(
  SELECT Item, ReturnDate, 
    ROW_NUMBER() OVER (PARTITION BY Item ORDER BY ReturnDate DESC) AS ReturnNo
  FROM Returns
)
SELECT rOuter.Item, rOuter.ReturnDate, DATEDIFF(month, prev.PrevDate, ReturnDate) AS Months
FROM RankedReturn rOuter
OUTER APPLY 
  (
    SELECT ReturnDate AS PrevDate 
    FROM RankedReturn rInner 
    WHERE rOuter.Item = rInner.Item AND rOuter.ReturnNo = rInner.ReturnNo - 1
  ) prev

    Oops, and the SQL Fiddle is here.

    Edited because the month difference calculation was backwards; fixed now